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

/**

* @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