Mysql-foreign KEY

Role: Maintain data consistency, integrity. Implement one-to-one or one-to-many relationships. (In the course of learning, the teacher said, the actual production, generally do not use physical foreign key constraints, are the use of logical foreign KEY constraints)

Requirements:

1. The storage engine for the parent table and the child table must be equal, and can only be InnoDB;
2. The use of temporary tables is prohibited;
3. The foreign key column and the reference column have the same data type. the length of the number and whether the signed bit must be the same. The length of the character can be different;
4. The foreign key column and the reference column must create an index. If there is no index for the foreign key column, MySQL automatically creates the index.

Referential operation of the constraint (when inserting the data, the parent table is inserted first, and the child table is inserted)

1. CASCADE: Delete or update from parent table, and automatically delete or update matching rows in child table
2. Set NULL: Deletes or updates rows from the parent table, and sets the foreign key column in the child table to null. If you use this option, you must ensure that the child table column does not specify not NULL;
3. RESTRICT: To deny the deletion or update of the parent table;
4. NO ACTION: Standard SQL keyword, in MySQL, same as restrict;

Eg:foreign KEY (Pid) REFERENCES Province (Id) on DELETE CASCADE;

Mysql-foreign KEY