A Brief Introduction to foreign key constraints of MySQL Databases

This article mainly introduces someMySQL databaseOfForeign key constraintNext, let's take a look at it.

Foreign key constraints must meet the following conditions:

Both parent and child tables are innodb, not temporary tables.

In a sub-table, an index must exist. The foreign key column must be the whole or part of the index column, but must be the starting part and the sequence is consistent. From mysql4.1.2, if no index exists, indexes on Foreign keys are automatically created. constraints are accelerated to check and avoid full table scanning.

In the parent table, an index must exist. The referenced key must be the whole or part of the index column, but must be the starting part and the sequence is consistent. The accelerated constraint check/avoids full table scanning.

Foreign key prefixes or suffixes cannot be indexed. Foreign keys cannot contain text/blob columns.

If the constraint identifier symbol is specified, the database range must be unique. If it is not specified, the system automatically generates the constraint.

The corresponding columns of the Parent and Child tables must have similar internal data types, so that they do not need to be followed up or converted for comparison.

For numeric types, the type length must be the same as the symbol.

For character types, the length does not have to be the same.

If you create an on delete set null or on update set null constraint, the column of the sub-table must not be null.

Mysql does not check if the foreign key or candidate key (referenced key) column has a null value starting from mysql3.23.50.

Meanings of foreign key constraints on subtables:

If no candidate key is found in the parent table, insert/update is not allowed in the child table.

Description of the foreign key constraint on the parent table:

When updating or deleting a parent table to update or delete one or more candidate keys that match the row in the child table, the behavior of the parent table depends on: the on update/on delete clause specified when defining the foreign key of the sub-Table. InnoDB supports five methods, which are listed as follows:

Cascade Mode

During the update/delete record operation on the parent table, the matching record of the child table is synchronized to update/delete;

On delete cascade is available from mysql3.23.50; on update cascade is available from mysql4.0.8.

Set null

When updating/deleting records in the parent table, set the columns of matched records in the child table to null;

Note that the foreign key column of the sub-table cannot be not null;

On delete set null is available from mysql3.23.50; on update set null is available from mysql4.0.8.

No action Method

If the child table has matched records, the update/delete operation on the candidate keys corresponding to the parent table is not allowed;

This is ANSI SQL-92 standard, supported from mysql4.0.8.

Restrict Mode

The same as no action, both check the foreign key constraints immediately;

Set default Mode

The parser recognizes this action, but Innodb cannot.

Note: trigger is not affected by the cascade action of the foreign key, that is, trigger is not released.

The foreign key constraints of MySQL databases are introduced here. I hope this introduction will bring you some gains. Thank you!