(MySQL) FOREIGN KEY Constraints Referential Actions

·

8 min read

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|

idnameemail
1name1email1
2name2email2
3name3email3

|result - article|

idtitlecontentwriter
1title 1content 11
2title 2content 22
3title 3content 33

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|

idnameemail
1name1email1
2name2email2
3name3email3

|result - article|

idtitlecontentwriter
1title 1content 11
2title 2content 22
3title 3content 33

step 2: UPDATE

|code|

UPDATE member SET id = 100 WHERE id = 2;

SELECT * FROM member;
SELECT * FROM article;

|result - member|

idnameemail
1name1email1
3name3email3
100name2email2

|result - article|

idtitlecontentwriter
1title 1content 11
2title 2content 2100
3title 3content 33

step 3: DELETE

|code|

DELETE FROM member WHERE id = 3;

SELECT * FROM member;
SELECT * FROM article;

|result - member|

idnameemail
1name1email1
100name2email2

|result - article|

idtitlecontentwriter
1title 1content 11
2title 2content 2100

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|

idnameemail
1name1email1
2name2email2
3name3email3

|result - article|

idtitlecontentwriter
1title 1content 11
2title 2content 22
3title 3content 33

step 2: UPDATE

|code|

UPDATE member SET id = 100 WHERE id = 2;

SELECT * FROM member;
SELECT * FROM article;

|result - member|

idnameemail
1name1email1
3name3email3
100name2email2

|result - article|

idtitlecontentwriter
1title 1content 11
2title 2content 2NULL
3title 3content 33

step 3: DELETE

|code|

DELETE FROM member WHERE id = 3;

SELECT * FROM member;
SELECT * FROM article;

|result - member|

idnameemail
1name1email1
100name2email2

|result - article|

idtitlecontentwriter
1title 1content 11
2title 2content 2NULL
3title 3content 3NULL

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|

idnameemail
1name1email1
2name2email2
3name3email3

|result - article|

idtitlecontentwriter
1title 1content 11
2title 2content 22
3title 3content 33

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`))