Shulou(Shulou.com)06/01 Report--

Editor to share with you what is the role of foreign keys in MySQL, I hope you will gain a lot after reading this article, let's discuss it together!

The role of MySQL foreign keys:

The main purpose of maintaining data consistency and integrity is to control the data stored in the foreign key table. Associate the two tables, and the foreign key can only refer to the values of the columns in the table!

Let's build two tables.

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

We built

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

Example2 table, which contains the id,stu_id student number, course_id course number, and then create a foreign key

Insert data into two tables respectively.

We call stu_id and course_id in example2 as the foreign keys of the example2 table. Example1 is the parent table and example2 is the word table. The two tables form an association. Only after the data of the word table can be deleted can the corresponding data in the parent table be deleted.

Now let's delete a piece of data in example1

Delete from example1 where stu_id=2

Will find that the error is reported.

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`example3`, CONSTRAINT `fck` FOREIGN KEY (`stu_ id`, `course_ id`) REFERENCES `example2` (`stu_ id`, `course_ id`))

Because the data in example2 is associated with the data of example1, it cannot be deleted, thus achieving the function of 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 a success.

Event trigger limit:

On delete and on update, you can set parameters cascade (follow foreign key changes), restrict (limit 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 what event trigger limits are for.

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

Alter table example2 drop foreign key fknock`; alter table example2 add CONSTRAINT `fck` FOREIGN KEY (`stu_ id`, `course_ id`) REFERENCES `example1` (`stu_ id`, `course_ id`) ON DELETE CASCADE ON UPDATE CASCADE

Let's take a look at the data.

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)

The stu_id and course_id in example1 and example2 are both 1

Let's modify the data in the example1 table.

Update example1 set stu_id=3,course_id=3 where stu_id=1

Let's 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 notice that stu_id and course_id in example1 and example2 have both become 3?

We are here 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 the example2 is gone.

In fact, ah, it is up to the event trigger to decide whether foreign keys play this role in maintaining data consistency and integrity, whether they are not allowed to be changed or whether they are changed together.

After reading this article, I believe you have a certain understanding of the role of foreign keys in MySQL, want to know more about it, welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.