MySQL foreign key details foreign key

Foreign keys have the mechanism of maintaining data integrity and consistency, and have a good check effect on business processing.

1. The table engine must be INNODB,MYISAM not supported

2, foreign keys must be indexed (can be normal, primary key, unique, not established in advance will automatically create a normal index), you want to use the foreign key and the reference of the external key, that is

ALTER TABLE B add constraint ' b_foreign_key_name ' foreign key (' BFK ') references A (' AFK ') on Delete no action on update no Action

When B_foreign_key_name is a foreign key name, both the BFK field and the AFK field must be indexed

3, the appearance of the constraint table, bound to contain the FOREIGN KEY constraint table, that is, B contains a reference table with a foreign key, then a-based B is from, weak association on the delete on update and other actions, then a change B will be changed, b How to change a does not have to follow the change, and table A must exist beforehand B the value of the data foreign key column to be inserted, such as B.BFK as the foreign key reference A.AFK, the value inserted by B.BFK must be an existing in A.AFK

4, the simple point of 3 is that if B has a as a reference to the foreign key, then the value of this field in B can only be a value in a, from table B will be in real time by the main Table a constraints, and if the associated with the delete on update and other operations when the referenced field in a delete or update , the corresponding record in B also occurs with the delete or update operation, integrity.

Here we take a simple student information management system of data table as an example

Add the table and index first.

Student Table  cid as the Foreign Key Association class table  pid as the foreign key of the   file table   So both of these goods have to be indexed create table my_student (' ID '   Int unsigned not null auto_increment primary key, ' name '  varchar   not null comment  ' Student name ', ' pid '  int unsigned not null  comment  ' Student profile id ', ' CID '  int unsigned not null comment  ' Student class id ',key  ' cid ' (' CID '),key  ' pid ' (' pid ')) engine=innodb default  charset=utf8 auto_increment=1;//class table  id as a   student table foreign Key Association   has a primary key index Create table my_class (' Id '  int unsigned not null auto_increment primary key, ' cname '  varchar (  not null comment  ' class name ', ' info '  tinytext not null  default  ') engine=innodb default charset=utf8 auto_increment=1;//file Table  id as a foreign key   association   Student table   PRIMARY key index Create tAble my_profile (' id '  int unsigned not null auto_increment primary key, ' PName '  varchar (+)  not null comment  ' profile name ', ' info '  tinytext  not null default  '  comment  ' student info ',) engine=innodb default  charset=utf8 auto_increment=1;

Here we will my_student as the appearance of My_profile, that is, the constraint table, that is, my_profile with its own ID as a foreign key association with the my_student PID field as a reference, the associated delete linkage operation, update does not do anything, as follows

ALTER TABLE My_profile add constraint profile_student foreign key (' id ') references my_student (' PID ') on the delete cascade on Update no action;

Here we will my_class as the appearance of My_student, that is, the constraint table, that is, my_student with its own CID as a foreign key association with the My_class ID field as a reference, associated with the update linkage operation, Delete does not do any action As follows

ALTER TABLE my_student add constraint student_class foreign key (' CID ') references My_class (' ID ') on UPDATE cascade on Del Ete no action;

When I delete a id=1 student in my_student, it deletes the record for this student's PID in My_profile.

Delete the student record with ID 1, because the file table is constrained by the student table, and associated with the DELETE CASCADE action Delete from my_student where id = 1;//The file table Delete ID record for this student record of the PID delete From my_profile WHERE id = (select PID from my_student where id = 1);

When I update the class of id=1 in My_class to 5 o'clock, it will update the cid=1 students in My_student to cid=5

Update linkage update My_class Set id = 5 WHERE id = 1;//Updated linkage update my_student Set cid = 5 WHERE cid = 1;

Post the Code:

My_profile:

ID as a foreign key, the reference my_student with its PID as the Association, the Association to delete linkage, update no action, the file table by the deletion of the student table constraint, when the Student table ID is xx record is deleted, the record in the File table ID for this record will also be deleted.

My_student:

Student table

PID as the foreign Key association of the file table, so to establish the key ' PID ' index

Use the CID as the foreign key to refer to the Class Table ID Association Update operation Delete No association (the intention is that when the class ID is changed, the CID of each student in the student table will also be associated with the update, so that the class ID in the class table changes, students belong to the classes remain intact and consistent)

My_class:

Class table, ID as foreign key reference for student table, index of primary key

Experiment begins:

1. Delete A student from the student table, the records in the file table that are referenced as appearances and associated with the delete linkage will be deleted, and the association is

My_profile.id = Record of My_student.pid

It's easy to see that when you delete a student with an ID of 22, his PID is 2, and a record with an ID of 2 in the file table is also associated with the deletion.

2, change the class ID, The student Table CID foreign key update linkage the ID in the class table, that is, when I change the class ID, the CID in the student table will also be updated

It's easy to see. The four-year ID was updated from 4 to 5 o'clock, and the CID in the Student table as the reference table, the lesser red in grade four, was also updated from 4 to 5.

There are four types of linkage operations on the delete on update

No action

Cascade

Set NULL

Restrict

Add foreign key

ALTER TABLE B add constraint ' BFK ' foreign key (' Fk_column_name ') references A (' column_name ') on Delete no action on Updat e no action;

Delete foreign key

ALTER TABLE B drop foreign key ' BFK ';

We can Baidu a bit, here is not wordy, deadline!

MySQL foreign key details foreign key