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

What is the foreign key (froeign key) in mysql

If there are two tables, one of which has a field that points to the primary key of the other table, this can be called a froeign key.

When you add a record to a child table, you need to determine whether there is a record corresponding to the parent table.

If the parent table does not have a corresponding record, the child table (from the table) cannot insert this data.

Here is an example of a foreign key:

1. First create a master table that holds the class information.

Create table class (id TINYINT PRIMARY KEY auto_increment, class_name varchar (20)) engine=innodb

two。 Insert five records in the class table, representing five classes.

Insert into class (id,class_name) value (1, "class1"), (2, "class2"), (3, "class3"), (4, "class4"), (5, "class5")

3. Create a child table that stores the correspondence between the students and the class. which class do these students belong to?

Create table student (id int primary key auto_increment, name varchar (20), class_id TINYINT) engine=innodb

4. Insert six records, representing six students.

Insert into student (id,name,class_id) value (1, "stu1", 2), (2, "stu2", 2), (3, "stu3", 1), (4, "stu4", 3), (5, "stu5", 1), (6, "stu6", 4)

The current rule is that a class can correspond to multiple students, but a student can only belong to one class.

5. Increase foreign key constraints.

Next, add a foreign key constraint to the student table.

Alter table student add constraint cls_stu foreign key (class_id) references class (id)

# the constraint field is mainly the id field of the class table.

6. Test whether the foreign key constraint is in effect.

The class class table we just created has a total of five records, that is, five classes. (class1~class5 respectively)

Now in the student table, add a new student, this student belongs to a class that does not exist in the class table, class 6, and see what happens:

Mysql > insert into student (id,name,class_id) value (7, "stu7", 6)

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db_ 1`.student`, CONSTRAINT `cls_ stu`FOREIGN KEY (`class_ id`) REFERENCES `class` (`id`))

Unable to add this record, which means that the class_id field of the student table has been constrained by the id field of the class table, and when the id field of the class table does not have this id, the student table cannot insert the record.

7. Delete a foreign key constraint for a table.

ALTER TABLE student DROP FOREIGN KEY [foreign key name]

Example:

Alter table student drop foreign key cls_stu

Some foreign key operations supported by the 8.innodb engine.

# when a table is constrained by a foreign key, insert/update is not allowed on the child table if the candidate key is not found in the parent table.

# attention! When update/delete is performed on the parent table to update or delete candidate keys that have one or more matching rows in the child table, the behavior of the parent table depends on the on update or on delete clause specified when defining the foreign key of the child table!

# here are four common ways supported by innodb engine:

When update/delete records are recorded on the parent table in 8.1cascade mode, the matching records of update/delete dropped child tables are synchronized.

Cascade deletion of foreign keys: if the records in the parent table are deleted, the corresponding records in the child table are automatically deleted

Alter table student add constraint cls_stu foreign key (class_id) references class (id) on delete cascade

# cascading delete or change, when the parent table foreign key position is modified, or deleted, the child table change or delete depends entirely on on delete or on update, of course, these two keywords can appear at the same time!

The following is an example of cascading delete keywords:

Alter table student add constraint cls_stu foreign key (class_id) references class (id) on delete cascade

8.2 when update/delete records on the parent table in set null mode, set the column of matching records on the child table to null.

Example:

Alter table student add constraint cls_stu foreign key (class_id) references class (id) on delete set null

8.3 Restrict mode: refuses to delete and update the parent table (not commonly used).

8.4 No action mode is the same as Restrict in mysql. If there are matching records in the child table, the update/delete operation on the candidate key corresponding to the parent table is not allowed.

9. Some additions to foreign keys:

Set up cascading operations:

When the master table data changes, how should the slave table data associated with it be handled

Use keywords:

On update

On delete

To identify

Allowed cascading actions:

Cascade association operation. If the master table is updated or deleted, the slave table will also perform the corresponding operation.

Set null, indicating that the slave table data does not point to any records of the master table

Restrict: rejects operations related to the main table

Alter table t_student add foreign key (class_id) references t_class (class_id)

On delete set null; # sets the foreign key value of the slave table to null when deleting the foreign key

Modify foreign keys:

Delete the foreign key of this table first, then add it

Alter table tb_name drop froeign key foreign key name

The name of a foreign key can be customized when establishing a foreign key. If it is not customized, a name will be automatically generated according to mysql

Show create table tb_name

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.