Clarify the meaning of mysql chain and command

Usually speaking of foreign keys, we only mention "the purpose of the foreign key is to determine the referential integrity of the data." However, what foreign keys specifically include the actions and meanings?

1, set the chain:

(1), create a foreign key through create table:

Look at the 2 lines of code is built in the order:

Note: ON update cascade ON Delete cascade Cascade update and cascade delete.

Usually set the outer chain, will first set the index of the outer chain field, as above: key idx_fk_country_id (country_id), but this is not necessary, the above code:

These can be omitted, starting directly from FOREIGN KEY. Of course, if you want to give the chain name, the constraint is necessary,

If there is no key idx_fk_country_id (country_id), this line, after setting the outer chain, mysql will automatically add the key index (that is, the standard index index.) You can use the show create table city table;

(2), alter table create foreign key: - Note that the command is not used modify. But add

ALTER TABLE city ADD INDEX idx_city (countryid);

ALTER TABLE city ADD CONSTRAINT fk_city_country FOREIGN KEY (countryid) REFERENCES country (country_id) ON DELETE CASCADE;

2, delete the foreign key:

ALTER TABLE tbname DROP FOREIGN KEY fk_fkname;

However, if the definition did not specify CONSTRAINT fk_fkname (foreign key symbols) how to delete it? Do not worry, if not specified, MySQL will create one, you can view the following command: SHOW CREATE TABLE tbname; And then execute the above command.

3, the specific action outside the chain:

Note that on DELETE restrict and on DELETE cascade can not be defined at the same time

Default action 1:

When increasing the sub-table foreign key value, if the primary key does not exist in the parent table, then the prohibition of the insertion.

Default action 2:

When the main table to delete a record, if the sub-table has the corresponding recorded content, prohibit the deletion

Therefore, the above can be on DELETE restrict omitted.

Non-default action - Cascading Update:

When the parent table to update the primary key, if the child table has a corresponding record, then automatically update the child table, this action with ON update cascade to achieve.

Non-default action - Cascade delete:

When the parent table updates the primary key, if the child table has a corresponding record, then automatically update the child table, this action with ON Delete cascade to achieve.

4, foreign key other parameters:

a, set null: that the parent table is updated or deleted, the corresponding sub-table field is set null.

b, no action: the same as restrict restrict the parent table can not be updated in the case of a sub-table associated records.

5, temporarily closed foreign key check:

Commonly used in:

A, import multiple table data, you need to ignore the table before the import order,

B, the implementation of LOAD DATA and ALTER TABLE operation.

command:

set foreign_key_checks = 0

When done, re-open the foreign key check with set foreign_key_checks = 1; to recover