Clarify the meaning and commands of mysql external links

Generally speakingExternal linkThe purpose of the external chain is to determine the integrity of the reference (referential integrity ).", But what actions and meanings does the external link contain?

1. Set external links:

(1) create a foreign key through create table:

The following two lines of code are the commands used to create a table:

Create table city (
......
Key idx_fk_country_id (country_id ),
Constraint fk_city_country Foreign Key (country_id) References country (country_id) on DELETE restrict ON update cascade ON Delete cascade
)...

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

Usually, when setting the external link, the index of the external link field is set first, such as: key idx_fk_country_id (country_id), but this is not required. In the above Code:

Key idx_fk_country_id (country_id ),
Constraint fk_city_country

These can be omitted, directly starting from foreign key. Of course, if you want to name external links, constraint is required,

If no key idx_fk_country_id (country_id) exists, mysql automatically adds the key index (Standard index) after setting the external link ). you can use show create table city; to view the created table.

(2) create a foreign key through alter table: -- note that the command does not use modify. Instead, 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 Foreign keys:

Alter table tbname drop foreign key fk_fkname;

But if the definition does not specify the CONSTRAINT fk_fkname, that is, the foreign key symbol), how should we delete it? Don't worry, MySQL will CREATE one by itself. You can run the following command: show create table tbname; and then execute the above command.

3. Specific actions of external links:

Note: on DELETE restrict and on DELETE cascade cannot be defined at the same time

Default Action 1:

When you add a foreign key value to a sub-table, if the primary key of the parent table does not exist, insertion is prohibited.

Default Action 2:

When a record is deleted in the primary table, it is prohibited to delete the content that should be recorded in the subtable.

Therefore, the above can be omitted on DELETE restrict.

Non-default action-cascade update:

When the parent table updates the primary key, if the child table has a corresponding record, the child table is automatically updated. This action is implemented by ON update cascade.

Non-default action-cascade deletion:

When the parent table updates the primary key, if the child table has a corresponding record, the child table is automatically updated. This operation is implemented by ON Delete cascade.

4. Other foreign key parameters:

A. set null: when the parent table is updated or deleted, the field corresponding to the child table is set null.

B. no action: The same as restrict. The parent table cannot be updated when the sub-table has associated records.

5. temporarily disable the foreign key check:

Commonly Used in:

A. When importing data from multiple tables, you must ignore the import sequence of the previous tables,

B. When performing the LOAD DATA and ALTER TABLE operations.

Command:

Set foreign_key_checks = 0

After that, enable the foreign key check again and use set foreign_key_checks = 1; to restore

I believe that you have gained more understanding of the meaning and commands of mysql external links through the above learning. This article is suitable for beginners who have just started reading, it is a must-have knowledge for beginners and I hope it will help you.