Using Navicat Many-to-many relational SQL statements implemented in MySQL

Source: Internet
Author: User

/**

* @author Blovedr

* function: simulate two table relationship and add two primary key and foreign key detailed process

* Date: July 26, 2018 18:45

* Note: Learn the database MySQL point record, thank you online the great God to share experience and information, you are welcome to the great God to criticize the guidance and exchange.

*/

Create a new database cc in Navicat, and in cc new query, enter the following SQL statement:

first table ---class table (OK)

CREATE TABLE Banji

(

banji_id int PRIMARY KEY,

Banji_num int NOT NULL,

Banji_name nvarchar (150)

)

second table ---Teacher's table (OK)

CREATE TABLE Jiaoshi

(

jiaoshi_id int PRIMARY KEY,

Jiaoshi_name nvarchar (150)

)

The third table---to simulate the relationship between class and teacher 1 Mapping---mapping ---(ok, no foreign key at this time )

CREATE TABLE Banji_jiaoshi_mapping

(

banji_id int,

jiaoshi_id int,

Kecheng nvarchar (60),

Constraint pk_banji_id_jiaoshi_id primary KEY (banji_id, jiaoshi_id)

)

drop table banji_jiaoshi_mapping;

The third table---to simulate the relationship between class and teacher 2 mapping---mapping ---(error, when SQL Statement using Navicat to run an error in MySQL )

CREATE TABLE Banji_jiaoshi_mapping2

(

banji_id int constraint fk_banji_id foreign key references Banji (banji_id),

jiaoshi_id int foreign key references Jiaoshi (jiaoshi_id),

Kecheng nvarchar (60),

Constraint pk_banji_id_jiaoshi_id primary KEY (banji_id, jiaoshi_id)

)

The third table---to simulate the relationship between class and teacher 3 mapping---mapping ---(ok, There are two primary and foreign keys at this time )

CREATE TABLE Banji_jiaoshi_mapping3

(

banji_id int,

jiaoshi_id int,

constraint fk_banji_id foreign key (banji_id) references Banji (banji_id),

Constraint fk_jiaoshi_id foreign KEY (jiaoshi_id) references Jiaoshi (jiaoshi_id),

Kecheng nvarchar (60),

constraint pk_banji_id_jiaoshi_id primary key (banji_id, jiaoshi_id, Kecheng)

)

--- Delete SQL statements for table Banji_jiaoshi_mapping3

drop table banji_jiaoshi_mapping3;

Using Navicat Many-to-many relational SQL statements implemented in MySQL

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.