Two methods of setting Foreign key to mysql Database

Here are two ways to set foreign keys to a mysql database. The secret of the text is to be close to the topic. So, no gossip, let's just read the following, I believe that after reading the two ways to set foreign keys to the mysql database this article will certainly benefit you.

1. Actions when creating a table

Class table (master table):

Create table tb1 (id INT PRIMARY KEY AUTO_INCREMENT, classname VARCHAR (20) NOT NULL)

User table (child table):

Create table tb2 (id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR (20) NOT NULL, classid INT, FOREIGN KEY (classid) REFERENCES tb1 (id) ON DELETE CASCADE ON UPDATE CASCADE)

2. Add foreign keys to the existing tables

ALTER TABLE tb3 ADD CONSTRAINT tipped C FOREIGN KEY (classid) REFERENCES tb1 (id)

After that, you can use the command to view the operation of creating the table, and SHOW CREATE TABLE tb2; can find this sentence in it.

PRIMARY KEY (`id`), KEY `classid` (`classid`), CONSTRAINT `tb2_ibfk_ 1` FOREIGN KEY (`classid`) REFERENCES `tb1` (`id`) ON DELETE CASCADE

Indicates that the foreign key has been created successfully.

Additional instructions are attached:

CASCADE: deletes or updates from the parent table and automatically deletes or updates matching rows in the child table.

SET NULL: delete or update rows from the parent table and set the foreign key column NULL in the child table. The premise is to ensure that the foreign key column is not NOT NULL.

RESTRICT: rejects delete or update operations on the parent table.

Is there anything you don't understand about the above two methods of setting foreign keys to the mysql database? Or if you want to know more about it, you can continue to follow our industry information section.