Summary of foreign key constraints and cascading operations

Posted by irn3rd on Fri, 18 Feb 2022 05:51:50 +0100

1 Concept

Foreign key constraints are used to establish links between two table data. One field of one table is constrained by the corresponding field in the other table. In other words, there must be at least two kinds of tables for setting foreign key constraints. The constrained table is called a slave table (child table) and the other is called a master table (parent table), which belongs to the master-slave relationship.
Adding foreign key constraints helps to ensure the integrity of the data table, that is, adding data in the master table that is not in the slave table,

2 foreign key creation rules

You must have a master table to set a slave table.
The master table must actually exist.
You must define a primary key for the primary table.
The data type of the foreign key column must be the same as that of the primary key column.
The number of foreign key columns must be the same as the number of primary key columns.
The foreign key may not be the primary key in the appearance, but it must correspond to the field associated with the main table.
When creating master-slave tables, the storage engine must be InnoDB.

3 create foreign key constraint syntax

Standard syntax:

Standard syntax:
CONSTRAINT Foreign key name FOREIGN KEY (Foreign key column name of this table) REFERENCES Main table name(Primary key column name attribute of main table)

CONSTRAINT foreign key name can be omitted

Attribute description:

CASCADE operation: when deleting or modifying records in the master table, the foreign key fields of associated records will also be modified in the slave table.

RESTRICT: delete or modify the records of the main table. If there are associated records in the sub table, the main table is not allowed to be deleted or modified. (if you do not write the attribute, it defaults to this.)

SET NULL: when the master table deletes or modifies the master table record, the foreign key field of the associated record will be set to null in the slave table.

ON UPDATE CASCADE: when modifying records in the master table, the foreign key fields of the associated records in the slave table will also be modified. (change CASCADE to RESTRICT, meaning the opposite)

ON DELETE CASCADE: when deleting records from the main table, the foreign key fields of the associated records from the table will also be deleted. (change CASCADE to RESTRICT, meaning the opposite)

4 example description

4.1 foreign key constraints

-- establish db2 database
CREATE DATABASE db2;

-- use db2 database
USE db2;

/*
	Foreign key constraint
	Standard syntax:
		CONSTRAINT FOREIGN KEY name (FOREIGN KEY column name of this table) REFERENCES main table name (primary key column name of main table)
*/
-- Add foreign key constraints when creating tables
-- establish user User table
CREATE TABLE USER(
	id INT PRIMARY KEY AUTO_INCREMENT,    -- id
	NAME VARCHAR(20) NOT NULL             -- full name
);
-- Add user data
INSERT INTO USER VALUES (NULL,'Zhang San'),(NULL,'Li Si');


-- establish orderlist Order form
CREATE TABLE orderlist(
	id INT PRIMARY KEY AUTO_INCREMENT,    -- id
	number VARCHAR(20) NOT NULL,          -- Order number
	uid INT,			      -- Foreign key column
	CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id)
);
-- Add order data
INSERT INTO orderlist VALUES (NULL,'hm001',1),(NULL,'hm002',1),
(NULL,'hm003',2),(NULL,'hm004',2);


-- Add an order, but there is no real user. Add failed
INSERT INTO orderlist VALUES (NULL,'hm005',3);

-- Delete Li Si user. Delete failed
DELETE FROM USER WHERE NAME='Li Si';




/*
	Delete foreign key constraint
	Standard syntax:
		ALTER TABLE Table name DROP FOREIGN KEY name;
*/
-- Delete foreign key constraint
ALTER TABLE orderlist DROP FOREIGN KEY ou_fk1;



/*
	After adding external key constraint
	Standard syntax:
		ALTER TABLE Table name ADD CONSTRAINT FOREIGN KEY name FOREIGN KEY REFERENCES main table name (primary key column name);
*/
-- Add foreign key constraint
ALTER TABLE orderlist ADD CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id);

4.2 cascade operation

	/*
	Add foreign key constraints and cascade update standard syntax:
	ALTER TABLE Table name ADD CONSTRAINT FOREIGN KEY name FOREIGN KEY REFERENCES main table name (primary key column name) 
	ON UPDATE CASCADE;
	
	Add foreign key constraints and add cascading deletion standard syntax:
	ALTER TABLE Table name ADD CONSTRAINT FOREIGN KEY name FOREIGN KEY REFERENCES main table name (primary key column name) 
	ON DELETE CASCADE;
	
	Add a foreign key constraint, and add cascading updates and cascading deletions at the same time. Standard syntax:
	ALTER TABLE Table name ADD CONSTRAINT FOREIGN KEY name FOREIGN KEY REFERENCES main table name (primary key column name) 
	ON UPDATE CASCADE ON DELETE CASCADE;
*/
-- Delete foreign key constraint
ALTER TABLE orderlist DROP FOREIGN KEY ou_fk1;

-- Add foreign key constraints, and add cascading updates and cascading deletions at the same time
ALTER TABLE orderlist ADD CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id)
ON UPDATE CASCADE ON DELETE CASCADE;


-- The user Li Si id Change to 3,In order table uid It can also be modified automatically
UPDATE USER SET id=3 WHERE id=2;

-- Delete the user Li Si,The order of the user in the order table will also be deleted automatically
DELETE FROM USER WHERE id=3;

5. Why not recommend using foreign keys and concatenation?

Alibaba development manual is described as follows:
[mandatory] foreign keys and cascading are not allowed. All foreign key concepts must be solved at the application layer.

(1)Foreign keys and cascaded updates are suitable for single machine low concurrency, not for distributed and high concurrency clusters; 
(2)Cascading updates are strongly blocked, and there is a risk of database update storm; 
(3)Foreign keys affect the insertion speed of the database

Topics: Database MySQL

Programmer Think