Foreign key foreign key in MySQL

If a field in a table that has a non-primary key points to a primary key in a table, the field is called a foreign key.

You can have multiple foreign keys in a single table.

The default effect of a foreign key is two points:

1. The role of the child table (the table in which the foreign key is located): The operation fails if the foreign key field does not find a match in the parent table when it is writing.

2. Effect on 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.

The custom action of the foreign key----Three kinds of constraint modes:

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.

Prerequisites for using foreign keys:

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.

Second, the new foreign key

1. Added at creation time

CREATE TABLE MY_TAB1 (

ID int primary KEY auto_increment,

Name varchar (ten) is not NULL,

class int,

　　　foreign Key (class) references my_tab2;　

) CharSet UTF8;

2. In the created table, add

ALTER TABLE MY_TAB1 add [constraint foreign key name] foreign key (foreign key field name) references Mytab2 (primary key field name);

Third, delete the foreign key

ALTER TABLE My_tab drop FOREIGN key foreign key name;

Foreign key foreign key in MySQL