The relationship of the MySQL table

The relationship of the MySQL table

Relationship of the table:

One-to-one: 1:1

Eg: a person has only one identity card

Note: one-to-one key fields can be implemented by adding key fields.

--Method OneCREATE TABLET_person (IDINT PRIMARY KEYAuto_increment,unameVARCHAR(Ten));CREATE TABLET_card (IDINT PRIMARY KEYauto_increment,start_time date,end_time DATE,pid INT Unique,//key field This field is uniqueFOREIGN KEY(PID)REFERENCEST_person (ID) //Create foreign key to ensure data integrity and legality ); --Method TwoCREATE TABLET_card2 (card_idINT PRIMARY KEYauto_increment,start_time date,end_time DATE,FOREIGN KEY(card_id)REFERENCEST_person (ID));

Pair more: 1:n

Eg: how many grades a student has

Note: To implement a one-to-many key field by adding key fields to the top

--One -to-many--How many grades does a student have ?CREATE TABLEt_student (IDINT PRIMARY KEYAuto_increment,unameVARCHAR(Ten));CREATE TABLEt_scores (IDINT PRIMARY KEYAuto_increment,numINT,stu_id INT,//key fieldFOREIGN KEY(stu_id)REFERENCESt_student (ID));

Many-to-many: N +

Eg: multiple teachers teach multiple students

Note: To implement many-to-many by adding key fields you need to create a new table to add two key fields

--Many-to-manyCREATE TABLET_teacher (IDINT PRIMARY KEYAuto_increment,tnameVARCHAR(Ten));CREATE TABLEt_student (IDINT PRIMARY KEYAuto_increment,snameVARCHAR(Ten));CREATE TABLET_stu_tea (IDINT PRIMARY KEYAuto_increment,stuidINT, //key field TeaidINT, //key fields FOREIGN KEY(STUID)REFERENCESt_student (ID),FOREIGN KEY(Teaid)REFERENCEST_teacher (ID));

The relationship of the MySQL table