Practical application commands and specific applications contained in MySQL external links

The following articles mainly describe the actual application commands and specific applications contained in MySQL external links. In actual operations, we often encounter foreign keys, it is generally mentioned that "the purpose of the foreign key is to determine the reference integrity (referential integrity) of the data )."

1. configure external links:

(1) determine whether to create a foreign key for the 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 configuring the MySQL external link, the index of the external link field is configured 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 the 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 will automatically add the key index (Rule index) After configuring the MySQL external link ). you can use show create table city; to view the created table.

(2) 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 CONSTRAINT fk_fkname is not specified in the explanation, that is, the foreign key symbol), how should we delete it? Don't worry. MySQL will CREATE one by itself. You can view the following commands: show create table tbname; and then execute the preceding command.

3. Detailed action of MySQL external link:

On DELETE restrict and on DELETE cascade cannot be explained 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 primary table deletes a record, 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 Sub-table has corresponding records, the sub-table is automatically updated. This operation is implemented by ON update cascade.

Non-default action-cascade deletion:

When the parent table updates the primary key, if the Sub-table has corresponding records, the sub-table is automatically updated. This operation is implemented by ON Delete cascade.

4. Other foreign key parameters:

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

B. no action: similar to restrict, the parent table cannot be updated only when the sub-table has associated records.

5. temporarily disable the foreign key test:

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 it. The above content describes the commands and Application of MySQL external links. I hope you can gain some benefits.