Complement 12. Foreign KEY constraints on MySQL

First, what is the foreign key in MySQL (froeign key)

If there are two tables, one of the table's fields points to the primary key of the other table, which can be called the foreign key (Froeign key).

When you add a record to a child table, you need to determine whether there is a record corresponding to the parent table.

If the parent table does not have a corresponding record, the child table (from the table) cannot insert the data.

Here is an example of a foreign key:

1. First create a master table, which holds the class information.

CREATE TABLE class (ID TINYINT PRIMARY KEY auto_increment, class_name varchar) engine=innodb;

2. Insert five records in the class table, representing 5 classes.

INSERT into Class (Id,class_name) value (1, "Class1"), (2, "Class2"), (3, "Class3"), (4, "Class4"), (5, "class5");

3. Create a sub-table that holds the correspondence between the student and the class, which class does the student belong to?

CREATE TABLE student (ID int primary key auto_increment, name varchar (), class_id TINYINT) Engine=innodb;

4.6 records were inserted, representing 6 students.

INSERT into student (id,name,class_id) value (1, "STU1", 2), (2, "STU2", 2), (3, "Stu3", 1), (4, "Stu4", 3), (5, "Stu5", 1), (6, " Stu6 ", 4);

The rule now is that a class can correspond to multiple students, but a student can only belong to one class.

5. Add foreign KEY constraints.

Next, add a foreign key constraint to the student table.

ALTER TABLE student ADD constraint Cls_stu foreign key (class_id) references class (ID);

#约束字段以class表的id字段为主.

6. Test if the foreign key constraint is in effect.

We have just created a class table with a total of 5 records, that is, five classes. (CLASS1~CLASS5, respectively)

Now student table, a new student, this student belongs to a class table does not exist in class, Class 6, see what Effect:

mysql> INSERT into student (id,name,class_id) value (7, "Stu7", 6);

ERROR 1452 (23000): Cannot add or update a child row:a FOREIGN KEY constraint fails (' db_1 '. ' Student ', constraint ' Cls_st U ' FOREIGN KEY (' class_id ') REFERENCES ' class ' (' ID '))

This record cannot be added, which means that the class_id field of the student table is already constrained by the ID field of the class table, and the student table cannot insert this record when the ID field of the class table does not have this ID.

7. Delete a foreign KEY constraint for a table.

ALTER TABLE student DROP FOREIGN key "foreign Key name"

Cases:

ALTER TABLE student drop foreign key cls_stu;

Some of the foreign key operations supported by the 8.INNODB engine.

#当一个表被设置了外键约束, if a candidate key is not found in the parent table, the insert/update operation on the child table is not allowed.

# NOTE!!! When you update/delete on a parent table to update or delete a candidate key that has one or more matching rows in the child table, the behavior of the parent table is determined by the on UPDATE or ON DELETE clause that is specified when the foreign key of the child table is defined!!!

#下面是innodb引擎所支持的常见的四种方式:

8.1cascade mode Update/delete records on the parent table, synchronizing update/delete out the matching records of the child tables

Cascade Delete for foreign keys: If the records in the parent table are deleted, the corresponding records in the child table are automatically deleted

ALTER TABLE student ADD constraint Cls_stu foreign key (class_id) references class (ID) on DELETE cascade;

#级联删除或更改, when the parent table's foreign key position is modified, or deleted, the child table changes or deletions are entirely dependent on the on delete or on update, of course, these two keywords can appear at the same time!!!

Here is an example of a cascading delete keyword:

ALTER TABLE student ADD constraint Cls_stu foreign key (class_id) references class (ID) on DELETE cascade;

8.2 Set NULL to Update/delete records on the parent table, set the column of the matching record on the child table to null.

Cases:

ALTER TABLE student ADD constraint Cls_stu foreign key (class_id) references class (ID) on delete set null;

8.3 Restrict mode: rejects the delete update operation for the parent table (not commonly used).

8.4 No action mode in MySQL with restrict, if there is a matching record in the child table, it is not allowed to update/delete action on the parent table corresponding to the candidate key (learn)

9. Some additions to the foreign key:

To set cascading actions:

How the table data associated with the primary table data should be handled when it changes

Use keywords:

On update

On delete

To identify

allowable Cascade actions:

Cascade associated operation, if the primary table is updated or deleted, the corresponding action is also taken from the table

Set NULL, indicating that no records from table data are pointing to the primary table

Restrict: Rejecting the related operation of the primary table

ALTER TABLE T_student add foreign key (class_id) references T_class (class_id)

On delete set null; # When a foreign key is deleted, the foreign key value from the table is set to NULL

To modify a foreign key:

Remove the foreign key from the table before adding

ALTER TABLE Tb_name drop froeign key FOREIGN key name

Foreign key names can be customized when creating foreign keys, and if not customized, a name is automatically generated according to MySQL

Show CREATE TABLE tb_name;

This article is from the "Rebirth" blog, make sure to keep this source http://suhaozhi.blog.51cto.com/7272298/1932850

Complement 12. Foreign KEY constraints on MySQL