Example explanation of MySQL foreign key constraints

MySQL's foreign key constraint is used to establish a link between two tables. If one table changes, the other table also changes. From this feature, it is mainly to ensure the consistency and integrity of table data.
For two tables related by foreign keys, the table where the primary key in the related field is located is the primary table, also called the parent table, and the table where the foreign key is located is the secondary table, also called the child table. Several rules need to be followed when defining foreign keys:

1. The parent table must already exist in the database or be the table currently being created. If it is the latter case, the parent table and the child table are the same table. Such a table is called a self-referencing table, and this structure is called self-referencing.
2. A primary key must be defined for the parent table.
3. The primary key cannot contain null values, but null values ​​are allowed in foreign keys. That is, as long as every non-null value of the foreign key appears in the specified primary key, the content of this foreign key is correct.
4. The number of columns in the foreign key must be the same as the number of columns in the primary key of the parent table.
5. The data type of the column in the foreign key must be the same as the data type of the corresponding column in the primary key of the parent table. All this is rather general, so let’s look at some examples.

mysql:yeyztest ::>>create table fk_test_1( 
 -> id int not null primary key auto_increment,
 -> name varchar() default '');
Query OK, rows affected (0.10 sec)

mysql:yeyztest ::>>create table fk_test_2(
 -> id int not null primary key auto_increment,
 -> uid int, 
 -> foreign key fk_uid(uid) references fk_test_1(id));
Query OK, rows affected (0.06 sec)

Here we create two tables, one is fk_test_1 and the other is fk_test_2. A foreign key is set on the uid column of fk_test_2 to associate the id column of the table of fk_test_1. It is obvious here that fk_test_1 is the parent table and fk_test_2 is the child table. Next, we will conduct a data insertion experiment.

mysql:yeyztest ::>>insert into fk_test_1 values ​​(,'aaa'),(,'bbb');
Query OK, rows affected (0.00 sec)
Records: Duplicates: Warnings: 

mysql:yeyztest ::>>select * from fk_test_1;
+----+------+
| id | name |
+----+------+
| | aaa |
| | bbb |
+----+------+
 rows in set (0.00 sec)

mysql:yeyztest ::>>insert into fk_test_2 values ​​(,),(,);
Query OK, rows affected (0.00 sec)
Records: Duplicates: Warnings: 

mysql:yeyztest ::>>insert into fk_test_2 values ​​(,);
Query OK, row affected (0.00 sec)


mysql:yeyztest ::>>insert into fk_test_2 values ​​(,);  
ERROR (): Cannot add or update a child row: a foreign key constraint fails (`yeyztest`.`fk_test_2`, CONSTRAINT `fk_test_2_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `fk_test_1` (`id`))

First, insert two pieces of data into the main table, namely id=1 and id=2, and then insert data into the child table. The child table can successfully insert data with uid=1 and uid=2, but it fails when inserting data with uid=3. That is to say, by default, when inserting into the child table, the inserted foreign key associated field value must be the value contained in the associated column of the parent table. Note the default situation here, which will be explained later.

Let’s look at the deletion situation.

mysql:yeyztest ::>>select * from fk_test_2;
+----+------+
| id | uid |
+----+------+
| | |
| | |
| | |
+----+------+
 rows in set (0.00 sec)

mysql:yeyztest ::>>delete from fk_test_2 where id=;
Query OK, row affected (0.00 sec)

mysql:yeyztest ::>>select * from fk_test_1;  
+----+------+
| id | name |
+----+------+
| | aaa |
| | bbb |
+----+------+
 rows in set (0.00 sec)

mysql:yeyztest ::>>delete from fk_test_1 where id=; 
ERROR (): Cannot delete or update a parent row: a foreign key constraint fails (`yeyztest`.`fk_test_2`, CONSTRAINT `fk_test_2_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `fk_test_1` (`id`))

It can be seen that there is no problem in deleting on the child table fk_test_2, but when deleting on the parent table fk_test_1, it shows that the value of id=1 cannot be deleted. The reason is that there is a foreign key constraint. That is to say, by default, when deleting from the parent table, the column value that already has a dependent association in the child table cannot be directly deleted. Note the defaults here, which will be explained below.

Since delete failed, try update.

mysql:yeyztest ::>>update fk_test_1 set id= where id=;   
ERROR (): Cannot delete or update a parent row: 
a foreign key constraint fails (`yeyztest`.`fk_test_2`, 
CONSTRAINT `fk_test_2_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `fk_test_1` (`id`))

mysql:yeyztest ::>>update fk_test_1 set name='ccc' where id=; 
Query OK, row affected (0.00 sec)
Rows matched: Changed: Warnings:

It can be seen that updating the primary key column of the parent table still cannot be successfully executed, but updating other columns can be successfully executed.

At this point, we already know that the existence of foreign keys is to ensure the integrity and unity of the data, but it also brings a little problem, that is, any column in the parent table that is dependent on the child table cannot be deleted. This is not what we want. Some data will indeed expire, and we need to delete it, so what should we do at this time?

In the above test, we repeatedly mentioned a word, that is, by default, we did not set the deletion and update rules of foreign keys. Here MySQL helped us use the most stringent rule, which is restrict. In fact, there are some other rules, all of which are listed here:

• Delete the parent table:

cascade, set null, no action, restrict

• Update the parent table:

cascade, set null, no action, restrict

in

• Restrict is the default operation, which means that the parent table is denied to delete or modify the foreign key columns that the child table depends on. This is the safest setting;
• Cascade means that the records in the child table are deleted directly when the parent table is deleted. This is the most dangerous setting.
• set null means that when the parent table is deleted, the child table is processed with null value;
• No action means that when the parent table is deleted, no changes are made to the child table.

The syntax for setting an association is as follows:

alter table table name add constraint FK_ID foreign key (foreign key field name) references foreign table name (primary key field name)
[on delete {cascade | set null | no action | restrict}]
[on update {cascade | set null | no action | restrict}]

Now let's test the other three cases, starting with the cascade case:

mysql:yeyztest ::>>select * from fk_test_1;
+----+------+
| id | name |
+----+------+
| | ccc |
| | bbb |
+----+------+
 rows in set (0.00 sec)

mysql:yeyztest ::>>select * from fk_test_2;
+----+------+
| id | uid |
+----+------+
| | |
| | |
+----+------+
 rows in set (0.00 sec)

mysql:yeyztest ::>>show create table fk_test_2\G
*************************** 1. row ***************************
  Table: fk_test_2
Create Table: CREATE TABLE `fk_test_2` (
 `id` int() NOT NULL AUTO_INCREMENT,
 `uid` int() DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `fk_uid` (`uid`),
 CONSTRAINT `fk_test_2_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `fk_test_1` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT= DEFAULT CHARSET=utf8
 row in set (0.00 sec)

mysql:yeyztest ::>>alter table fk_test_2 drop foreign key fk_test_2_ibfk_1;
Query OK, rows affected (0.02 sec)
Records: Duplicates: Warnings: 

mysql:yeyztest ::>>alter table fk_test_2 add constraint fk_uid foreign key (uid) references fk_test_1(id) on delete cascade;
Query OK, rows affected (0.03 sec)
Records: Duplicates: Warnings: 

#######################################
####Delete the record of parent table id= here and check the result of child table###
#######################################
mysql:yeyztest ::>>delete from fk_test_1 where id=;
Query OK, row affected (0.00 sec)

mysql:yeyztest ::>>select * from fk_test_1;
+----+------+
| id | name |
+----+------+
| | ccc |
+----+------+
 row in set (0.00 sec)

mysql:yeyztest ::>>select * from fk_test_2;
+----+------+
| id | uid |
+----+------+
| | |
+----+------+
 row in set (0.00 sec)

It can be seen that at the beginning, the values ​​of the parent table include the values ​​of id=1 and id=2, and the values ​​of the child table include the values ​​of uid=2 and uid=1. When we delete the value of id=2 in the parent table, the value of uid=2 in the child table is also deleted directly. This is the role of cascade, that is, cascade deletion.

Let's take a look at the case of set null:

mysql:yeyztest ::>>alter table fk_test_2 drop foreign key fk_uid;   
Query OK, row affected (0.02 sec)
Records: Duplicates: Warnings: 

mysql:yeyztest ::>>alter table fk_test_2 add CONSTRAINT `fk_uid` FOREIGN KEY (`uid`) REFERENCES `fk_test_1` (`id`) ON DELETE set null;
Query OK, row affected (0.03 sec)
Records: Duplicates: Warnings: 

mysql:yeyztest ::>>delete from fk_test_1 where id=;
Query OK, row affected (0.00 sec)

mysql:yeyztest ::>>select *from fk_test_1;
Empty set (0.00 sec)

mysql:yeyztest ::>>select *from fk_test_2;
+----+------+
| id | uid |
+----+------+
| | NULL |
+----+------+
 row in set (0.00 sec)

It can be seen that after setting set null, when the parent table deletes the value of id=1, the value of uid in the child table becomes null, and the record is not deleted.

The no action situation is similar, except that the records in the child table are not modified.

The above is the operation of deleting the parent table. When the parent table is updated, the child table can also choose the above four situations, which are basically the same as delete, and will not be repeated here. If you are interested, you can test it yourself.

Finally, it should be noted that the foreign key column of the child table can have null values.

mysql:yeyztest ::>>insert into fk_test_1 values ​​(,);
Query OK, row affected (0.00 sec)

mysql:yeyztest ::>>select *from fk_test_2;   
+----+------+
| id | uid |
+----+------+
| | NULL |
+----+------+
 row in set (0.00 sec)

mysql:yeyztest ::>>insert into fk_test_2 values ​​(,NULL);
Query OK, row affected (0.00 sec)

mysql:yeyztest ::>>insert into fk_test_2 values ​​(,NULL);
Query OK, row affected (0.00 sec)

mysql:yeyztest ::>>select * from fk_test_2;
+----+------+
| id | uid |
+----+------+
| | NULL |
| | NULL |
| | NULL |
+----+------+
 rows in set (0.00 sec)

The above is the detailed content of the example explanation of MySQL foreign key constraints. For more information about MySQL foreign key constraints, please pay attention to other related articles on 123WORDPRESS.COM!