mysql--FOREIGN Key

Concept

Keyword: foreign key, also called FOREIGN KEY constraint!

If a field of entity A is just pointing to the primary key of another entity B, then the field of entity A is called a foreign key;

So, simply put, a foreign key is a field in this table that points to the primary key of the exterior!

Above, the class field in the student table is actually a foreign key!

Where the entity set (class table) that is pointed to is called the primary entity set (the primary table), also called the parent entity set (the parent table), which is responsible for pointing to the entity set (student table), called from the entity set (from the table), also called the Child entity set (child table)

Role

The meaning of the foreign key is to constrain the entity within the relationship!

Why a FOREIGN KEY constraint is required, because it is necessary to ensure that it conforms to the normal logical relationship in reality when inserting records:

If there are 11 classes in the class field of a student above, it is not symbolic logic, because there are no 11 classes in the Class table at all!

Therefore, the foreign key constraints are mainly reflected in the following two aspects:

1, to increase the child table records, you need to determine if there is a corresponding parent table record!

2, when deleting or changing the parent table record, how the child table should handle the related records!

Defined

You should add a foreign key property on a field from the table to perform the primary key of the primary table!

1 --Defining a parent table2 Create TableWeb_class (3class_idintUnsignedPrimary KeyAuto_increment,4Class_teachervarchar( -) not NULL5 );6 7 --Defining child Tables8 Create TableWeb_student (9stu_idintUnsignedPrimary KeyAuto_increment,TenStu_namevarchar( -) not NULL, OneStu_homevarchar( -), Astu_class_idintunsigned, -     --defining foreign keys -     Foreign Key(stu_class_id)ReferencesWeb_class (class_id) the);

Set CASCADE operations

Cascade Operations (Associative actions): Manipulate a table to affect another table
1. Main Table Update
Syntax form: On update[cascade Operation]
2. Main Table Delete
Syntax form: On delete[cascade Operation]

There are three common types of cascading operations above:
Cascade: Synchronous operation, concatenation operation, that is, when the main table field is updated or deleted
Delete or update from the foreign key of the table

Set NULL: null if the field of the primary table is updated or updated or deleted, the foreign key field from the table is set to NULL
Of course, if there is no non-null constraint from the foreign key field of the table

Restrict: is to reject the main table update or delete

So, there are 9 common cascade operations,

--On update cascade on DELETE Cascade--On update cascade on delete set NULL--On update cascade on delete Restrict--On update set NULL on DELETE Cascade--On update set NULL on delete set NULL--On update set NULL on delete restrict--On update restrict on DELETE cascade--On update restrict on delete set null--On update restrict on delete restrict

So complete the definition of the foreign key method syntax:

1 -- foreign key (from the foreign key field of the table) references the primary table name (primary key of the primary table) 2  on Update Cascade | Set NULL | Restrict  on Delete Cascade | Set NULL | Restrict

To delete a foreign key:
Grammar:

1 /* 2 Delete foreign key 3 ALTER TABLE Name drop FOREIGN key foreign key name; 4 the foreign key name here is not the field name of the foreign key, but the system automatically assigns a name when the foreign key is defined . 5 can be \g by show create table name; 6 */ 7  altertabledropforeignkey ' web_student_ibfk_1 ';

--Add foreign keys

/* ALTER TABLE name add foreign key (from the foreign key field of the table) reference the primary table name (the primary table's primary key) cascade operation; ALTER TABLE name add foreign key (from the table's foreign key field) references Primary table name (primary key of Main table) on Update cascade|set null|restrict on delete cascade|set null|restrict*/alter  TableAddforeignkeyreferences  web_calss (class_id) on Update Cascade  on Delete Set null;

Test

1 -- update on the main table first 2 Update Set = 8 where = 7;

1 -- Querying child Tables 2 Select *  from Web_student;

1 -- first delete on the primary table 2 Delete where = 8;

1 -- Querying child Tables 2 Select *  from Web_student;

To test other cascading operations

Attention:
1. In some large projects, the idea of foreign key design may only be used, but often it is not really grammatical
Do a foreign key operation because the cascade operation of the foreign key may meet the bottom MySQL execution speed! and often use
Two SQL statements to override the foreign key cascade operations (SQL statements that are manipulated separately)

2. Foreign key constraints, only stored on the InnoDB storage engine to perform

mysql--FOREIGN Key