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

The following together to understand the role of mysql foreign key tools and operation methods, I believe we will certainly benefit from reading, text in the fine is not much, I hope mysql foreign key tools role and operation methods this short content is what you want.

Foreign key definition: two tables with an association relationship, in which a certain field a in one table points to the primary key b in the other table, we call a foreign key. In their relationship, the table where b is located is called the master table, and the field where a is located is called the slave table.

If there is no association operation defined between the two tables, then a is only a logical foreign key. After defining the association operation between them, a is the real foreign key we will discuss today.

syntax: foreign key (field name) references main table name (field name) on [update| delete] [ cascade | set null | restrict ]

(1)Defined cascade operations:

on delete defines the action of the slave table when the master table records are deleted

on update defines the action to perform when the master table updates a record

(2)Actions that can be set:

cascade : concatenation operation, that is, the child table acts the same as the main table, deleting or updating

set null : When the primary table changes, the foreign key field of the child table is set to null

restrict : restrict changes to the master table

Examples:

Step 1: Create two tables, class and student, and associate the class table with class_id as the foreign key.

create table class (

id int primary key auto_increment,

class_name varchar(20) not null

);

create table student (

id int primary key auto_increment,

name varchar(20) not null,

class_id int ,

foreign key (class_id) references class (id)

on delete cascade

on update set null

);

Step 2: Add data to both tables separately

insert into class values

(null,'A'),

(null,'B');

insert into student values

(null,'xiaohong',1),

(null,'xiaoming',2),

(null,'xiaogang',1);

At this point, we first look at the data in the table to compare it with our later tests.

Step 3: Delete and modify, and view the results

We just set it to on delete cascade, let's verify the result.

When we delete the data with id=1 in the class table, the data with class_id = 1 in the student table will be deleted by default.

Try another update:

The update set null.

From the data we can see that when we change the id of the record with id=2 in the class table to 3, the class_id of the record with class_id = 2 in the student table becomes null.

After reading this article, many readers will definitely want to know more about the relevant content. If you need more industry information, you can pay attention to our industry information column.

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.