Understand the role of foreign keys in MySQL

【Related learning recommendations: mysql learning】

The role of MySQL foreign keys:

Maintain data consistency and integrity. The main purpose is to control the data stored in the foreign key table. To associate two tables, foreign keys can only reference the values ​​of columns in the table!

Let’s build two tables

CREATE TABLE `example1` (
  `stu_id` int(11) NOT NULL DEFAULT '0',
  `course_id` int(11) NOT NULL DEFAULT '0',
  `grade` float DEFAULT NULL,
  PRIMARY KEY (`stu_id`,`course_id`)
);
CREATE TABLE `example2` (
  `id` int(11) NOT NULL,
  `stu_id` int(11) DEFAULT NULL,
  `course_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `f_ck` (`stu_id`,`course_id`),
  CONSTRAINT `f_ck` FOREIGN KEY (`stu_id`, `course_id`) REFERENCES `example1` (`stu_id`, `course_id`)
);
insert into example1 (stu_id,course_id,grade)values(1,1,98.5),(2,2,89);
insert into example2 (id,stu_id,course_id)values(1,1,1),(2,2,2);

We built the

example1 table, which contains stu_id student number, course_id course number, grade score

example2 table contains id, stu_id student number, course_id course number, and then establish foreign keys

Insert data into the two tables respectively.

We call stu_id and course_id in example2 the foreign keys of the example2 table. example1 is the parent table, and example2 is the word table. The two tables are related. The data in the word table must be deleted before the parent table can be deleted. The corresponding data in

Now let’s delete a piece of data in example1

delete from example1 where stu_id=2;

You will find an error

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`example3`, CONSTRAINT `f_ck` FOREIGN KEY (`stu_id`, `course_id`) REFERENCES `example2` (`stu_id`, `course_id`))

Because example2 The data in is associated with the data of example1, so it cannot be deleted and serves as a foreign key;

Then we delete the data in the example2 table first, and then delete the data in the example1 table

delete from example2 where stu_id=2;
delete from example1 where stu_id=2;

This is successful;

Event trigger restrictions:

On delete and on update, the parameter cascade (follow Foreign key changes), restrict (restrict foreign key changes in the table), set Null (set null value), set Default (set default value), [default] no action

Let’s take a look at event triggering restrictions What is it for. . .

We first delete the foreign key, and then re-establish the foreign key with event trigger restrictions

alter table example2 drop foreign key f_ck; alter table example2 add CONSTRAINT `f_ck` FOREIGN KEY (`stu_id` , `course_id`) REFERENCES `example1` (`stu_id`, `course_id`) ON DELETE CASCADE ON UPDATE CASCADE;

Let’s check the data first

mysql> select * from example1;select * from example2;

+--------+-----------+-------+

| stu_id | course_id | grade |

+--------+-----------+-------+

|      1 |         1 |  98.5 |

+--------+-----------+-------+

1 row in set (0.00 sec)

+----+--------+-----------+

| id | stu_id | course_id |

+----+--------+-----------+

|  1 |      1 |         1 |

+----+--------+-----------+

1 row in set (0.00 sec)

At this time, the stu_id and course_id in example1 and example2 are both 1,

Let’s modify the data in the example1 table and see

update example1 set stu_id =3,course_id=3 where stu_id=1;

Check the data again

mysql> select * from example1;select * from example2;

+--------+-----------+-------+

| stu_id | course_id | grade |

+--------+-----------+-------+

|      3 |         3 |  98.5 |

+--------+-----------+-------+

1 row in set (0.00 sec)

+----+--------+-----------+

| id | stu_id | course_id |

+----+--------+-----------+

|  1 |      3 |         3 |

+----+--------+-----------+

1 row in set (0.00 sec)

Did you find that, example1 and The stu_id and course_id in example2 have become 3

We are going to delete the data in the example1 table

delete from example1 where stu_id=3;

You will find that it can be deleted , and the data in example2 is gone;

In fact, this is the role of foreign keys, to maintain data consistency and integrity. Whether to prevent changes or to change them together is determined by the event trigger;

Related learning recommendations: Programming videos

The above is the detailed content of Understand the role of foreign keys in MySQL. For more information, please follow other related articles on the PHP Chinese website!