mysql-FOREIGN key Foreign key

1, definition: If one table has one field pointing to the primary key of another table, the primary key field in the child table is called the foreign key.

　　　Multiple foreign keys can exist in a single table

2, the role of foreign keys

　　Maintain consistency and integrity of data

A, the role of the child table (the table where the foreign key resides): When the child table is writing, the operation fails if the foreign key field cannot find a match in the parent table.

b, the role of the parent table: When the primary key field of the parent table is deleted and changed, the operation fails if the corresponding primary key is referenced in the child table

3, the foreign key custom action----Three kinds of constraint mode:

　　District: Strict mode (default), the parent table cannot delete or update a record referenced by a quilt table.

Cascade: Cascading mode, after the parent table operation, the data associated with the child table is also followed.

Set NULL: Empty mode, if the foreign key field is allowed to be NLL, the corresponding field of the child table is empty after the parent table operation

4, the use of foreign key premise:

1. The table storage engine must be InnoDB, otherwise create a foreign key without a constraint effect.

2. The column type of the foreign key must be exactly the same as the primary key type of the parent table.

3. The name of the foreign key cannot be duplicated.

4. When a field with existing data is set as a foreign key, you must ensure that the data in the field corresponds to the primary key data of the parent table.

5. New FOREIGN key

A, add when building a table

CREATE TABLE my_tab1 (        intPRIMARYKEY  auto_increment,        varchar( Ten  not NULL ,         int ,         FOREIGN KEY REFERENCES my_tab2 (primary key field name)) CharSet UTF8      

B. Add in the table under construction

ALTER  TABLE ADD [constraint  foreign key name ]FOREIGNkey(foreign key field name)    REFERENCES my_tab2 (primary key field name)

6. Delete foreign keys

TABLE DROP  FOREIGN key FOREIGN key name

　　

mysql-FOREIGN key Foreign key