(MySQL) FOREIGN KEY Constraints Referential Actions
Terms
Parent-Child Relationship
Tables that have foreign keys are called 'child tables', while tables that have data about the foreign keys are called 'parent tables'.
Referential Action
Referential actions are behaviors of child tables if parent tables change.
Here, 'change' means updating a row or deleting a row.
The examples are these.
ON UPDATE SET NULL
ON DELETE CASCADE
RESTRICT
Concept
If a parent table changes, the system refuses the change.
Example
step 1: settings
|code|
CREATE TABLE member (
id INT PRIMARY KEY,
name VARCHAR(10) NOT NULL,
email VARCHAR(30) NOT NULL
);
CREATE TABLE article (
id INT PRIMARY KEY,
title VARCHAR(50),
content TEXT,
writer INT NOT NULL,
FOREIGN KEY(writer) REFERENCES member(id)
ON UPDATE RESTRICT ON DELETE RESTRICT
);
INSERT INTO member VALUES (1, 'name1', 'email1');
INSERT INTO member VALUES (2, 'name2', 'email2');
INSERT INTO member VALUES (3, 'name3', 'email3');
INSERT INTO article VALUES (1, 'title 1', 'content 1', 1);
INSERT INTO article VALUES (2, 'title 2', 'content 2', 2);
INSERT INTO article VALUES (3, 'title 3', 'content 3', 3);
SELECT * FROM member;
SELECT * FROM article;
|result - member|
| id | name | |
| 1 | name1 | email1 |
| 2 | name2 | email2 |
| 3 | name3 | email3 |
|result - article|
| id | title | content | writer |
| 1 | title 1 | content 1 | 1 |
| 2 | title 2 | content 2 | 2 |
| 3 | title 3 | content 3 | 3 |
step 2: UPDATE
|code|
UPDATE member SET id = 100 WHERE id = 2;
|result - error|
Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`joinexp`.`article`, CONSTRAINT `article_ibfk_1` FOREIGN KEY (`writer`) REFERENCES `member` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT)
step 3: DELETE
|code|
DELETE FROM member WHERE id = 3;
|result - error|
Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`joinexp`.`article`, CONSTRAINT `article_ibfk_1` FOREIGN KEY (`writer`) REFERENCES `member` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT)
CASCADE
Concept
If a parent table changes, the child table reflects the change.
In case of 'UPDATE', the rows chage their data.
In case of 'DELETE', the rows disappear.
Example
step 1: settings
|code|
CREATE TABLE member (
id INT PRIMARY KEY,
name VARCHAR(10) NOT NULL,
email VARCHAR(30) NOT NULL
);
CREATE TABLE article (
id INT PRIMARY KEY,
title VARCHAR(50),
content TEXT,
writer INT NOT NULL,
FOREIGN KEY(writer) REFERENCES member(id) ON UPDATE CASCADE ON DELETE CASCADE
);
INSERT INTO member VALUES (1, 'name1', 'email1');
INSERT INTO member VALUES (2, 'name2', 'email2');
INSERT INTO member VALUES (3, 'name3', 'email3');
INSERT INTO article VALUES (1, 'title 1', 'content 1', 1);
INSERT INTO article VALUES (2, 'title 2', 'content 2', 2);
INSERT INTO article VALUES (3, 'title 3', 'content 3', 3);
SELECT * FROM member;
SELECT * FROM article;
|result - member|
| id | name | |
| 1 | name1 | email1 |
| 2 | name2 | email2 |
| 3 | name3 | email3 |
|result - article|
| id | title | content | writer |
| 1 | title 1 | content 1 | 1 |
| 2 | title 2 | content 2 | 2 |
| 3 | title 3 | content 3 | 3 |
step 2: UPDATE
|code|
UPDATE member SET id = 100 WHERE id = 2;
SELECT * FROM member;
SELECT * FROM article;
|result - member|
| id | name | |
| 1 | name1 | email1 |
| 3 | name3 | email3 |
| 100 | name2 | email2 |
|result - article|
| id | title | content | writer |
| 1 | title 1 | content 1 | 1 |
| 2 | title 2 | content 2 | 100 |
| 3 | title 3 | content 3 | 3 |
step 3: DELETE
|code|
DELETE FROM member WHERE id = 3;
SELECT * FROM member;
SELECT * FROM article;
|result - member|
| id | name | |
| 1 | name1 | email1 |
| 100 | name2 | email2 |
|result - article|
| id | title | content | writer |
| 1 | title 1 | content 1 | 1 |
| 2 | title 2 | content 2 | 100 |
SET NULL
Concept
Both in 'UPDATE' and 'DELETE', if a parent table changes, the corresponding rows of the child table change their data to 'NULL'.
Example
step 1: settings
|code|
! Be careful not to set 'NOT NULL' in the foreign key. That's because 'SET NULL' allows null.
CREATE TABLE member (
id INT PRIMARY KEY,
name VARCHAR(10) NOT NULL,
email VARCHAR(30) NOT NULL
);
CREATE TABLE article (
id INT PRIMARY KEY,
title VARCHAR(50),
content TEXT,
-- 'writer' should not be 'NOT NULL' because 'SET NULL' allows null.
writer INT,
FOREIGN KEY(writer) REFERENCES member(id) ON UPDATE SET NULL ON DELETE SET NULL
);
INSERT INTO member VALUES (1, 'name1', 'email1');
INSERT INTO member VALUES (2, 'name2', 'email2');
INSERT INTO member VALUES (3, 'name3', 'email3');
INSERT INTO article VALUES (1, 'title 1', 'content 1', 1);
INSERT INTO article VALUES (2, 'title 2', 'content 2', 2);
INSERT INTO article VALUES (3, 'title 3', 'content 3', 3);
SELECT * FROM member;
SELECT * FROM article;
|result - member|
| id | name | |
| 1 | name1 | email1 |
| 2 | name2 | email2 |
| 3 | name3 | email3 |
|result - article|
| id | title | content | writer |
| 1 | title 1 | content 1 | 1 |
| 2 | title 2 | content 2 | 2 |
| 3 | title 3 | content 3 | 3 |
step 2: UPDATE
|code|
UPDATE member SET id = 100 WHERE id = 2;
SELECT * FROM member;
SELECT * FROM article;
|result - member|
| id | name | |
| 1 | name1 | email1 |
| 3 | name3 | email3 |
| 100 | name2 | email2 |
|result - article|
| id | title | content | writer |
| 1 | title 1 | content 1 | 1 |
| 2 | title 2 | content 2 | NULL |
| 3 | title 3 | content 3 | 3 |
step 3: DELETE
|code|
DELETE FROM member WHERE id = 3;
SELECT * FROM member;
SELECT * FROM article;
|result - member|
| id | name | |
| 1 | name1 | email1 |
| 100 | name2 | email2 |
|result - article|
| id | title | content | writer |
| 1 | title 1 | content 1 | 1 |
| 2 | title 2 | content 2 | NULL |
| 3 | title 3 | content 3 | NULL |
NO ACTION
Concept
If a parent table changes, the system refuses the change.
Example
step 1: settings
|code|
CREATE TABLE member (
id INT PRIMARY KEY,
name VARCHAR(10) NOT NULL,
email VARCHAR(30) NOT NULL
);
CREATE TABLE article (
id INT PRIMARY KEY,
title VARCHAR(50),
content TEXT,
writer INT NOT NULL,
FOREIGN KEY(writer) REFERENCES member(id)
ON UPDATE NO ACTION ON DELETE NO ACTION
);
INSERT INTO member VALUES (1, 'name1', 'email1');
INSERT INTO member VALUES (2, 'name2', 'email2');
INSERT INTO member VALUES (3, 'name3', 'email3');
INSERT INTO article VALUES (1, 'title 1', 'content 1', 1);
INSERT INTO article VALUES (2, 'title 2', 'content 2', 2);
INSERT INTO article VALUES (3, 'title 3', 'content 3', 3);
SELECT * FROM member;
SELECT * FROM article;
|result - member|
| id | name | |
| 1 | name1 | email1 |
| 2 | name2 | email2 |
| 3 | name3 | email3 |
|result - article|
| id | title | content | writer |
| 1 | title 1 | content 1 | 1 |
| 2 | title 2 | content 2 | 2 |
| 3 | title 3 | content 3 | 3 |
step 2: UPDATE
|code|
UPDATE member SET id = 100 WHERE id = 2;
|result - error|
Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`joinexp`.`article`, CONSTRAINT `article_ibfk_1` FOREIGN KEY (`writer`) REFERENCES `member` (`id`))
step 3: DELETE
|code|
DELETE FROM member WHERE id = 3;
|result - error|
Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`joinexp`.`article`, CONSTRAINT `article_ibfk_1` FOREIGN KEY (`writer`) REFERENCES `member` (`id`))