What's the meaning of RESTRICT in Foreign Keys?

Question

There is 4 cases: (For example I'm talking about ON DELETE)

CASCADE: the referencing rows will be deleted automatically along with the referenced ones.
SET NULL: the value of referencing record will be set to NULL automatically along with the referenced ones.
NO ACTION: There will not be any change in the the referencing rows when the referenced ones are deleted.
RESTRICT: { I cannot understand its concept }

I read the documentation several times for that, but still I cannot understand what RESTRICT does. May you please someone explain it by an example?

I read somewhere NO ACTION and RESTRICT are the same in MySQL. Is that true?

From the reference you quoted: `if you have defined a RESTRICT type constraint, and there is a child row with several parent rows, InnoDB does not permit the deletion of any of those parent rows.` What is your interpretation of that? — Dan Bracuk, Jul 05 '16 at 15:01
@DanBracuk the MySQL manual is well known for being not the easiest language form to understand.... — Martin, Jul 05 '16 at 15:08

score 10 · Accepted Answer · edited Jun 22 '21 at 07:49

10

RESTRICT: It will not allow deleting this (parent) record without deleting dependent records (records which are referring foreign key from this)

For example, with these tables:

parent:
```
ID  NAME
1   AAAA
2   BBBBB
```
child:
```
ID  PARENT_ID
1   1
2   1
3   2
```

We can expect the following behaviors:

```
ON DELETE CASCADE
```
If we delete AAA from parent it will: delete entries 1 and 2 in child
```
ON DELETE SET NULL
```
If we delete AAA from parent it will: set the column PARENT_ID to null for entries 1 and 2 in child
```
ON DELETE SET DEFAULT
```
If we delete AAA from parent it will: set the column PARENT_ID to their default value for entries 1 and 2 in child
```
ON DELETE NO ACTION
```
If we delete AAA from parent it will: allow deleting and have no action on entries in child
```
ON DELETE RESTRICT
```
We can't delete AAA from parent without updating or deleting the entries 1 and 2 in child first

edited Jun 22 '21 at 07:49

Matiboux

112
8

answered Jul 05 '16 at 14:59

Mahesh Madushanka

2,902
2
14
28

I guess all cases *(`CASCADE`, `SET NULL`, `NO ACTION`)* also *will not allow to delete parent record without deleting dependent records*. – Martin AJ Jul 05 '16 at 15:03
no on delete cascade when we delete record in parent record it will delete related entries on child table – Mahesh Madushanka Jul 05 '16 at 15:05
@Stack added all 4 definition with example because it will help others as well – Mahesh Madushanka Jul 05 '16 at 15:11
Hi @MaheshMadushanka What you mean by saying set to the default value: ON DELETE SET DEFAULT: set the column PARENT_ID to their default value for entries 1 and 2 in the child. – java dev Sep 02 '21 at 17:06
I know this question from the past. But the past for the future. – java dev Sep 02 '21 at 17:09
ON DELETE NO ACTION will not allow the deletion of the parent. This is standard SQL. (I am afraid the above answer is erroneous.) – Uday Reddy Jun 03 '23 at 19:31

score 3 · Answer 2 · answered Jul 05 '16 at 15:00

3

RESTRICT: Rejects the delete or update operation for the parent table. Specifying RESTRICT (or NO ACTION) is the same as omitting the ON DELETE or ON UPDATE clause.

NO ACTION: A keyword from standard SQL. In MySQL, equivalent to RESTRICT. The MySQL Server rejects the delete or update operation for the parent table if there is a related foreign key value in the referenced table. Some database systems have deferred checks, and NO ACTION is a deferred check. In MySQL, foreign key constraints are checked immediately, so NO ACTION is the same as RESTRICT.

answered Jul 05 '16 at 15:00

Amar Srivastava

373
3
10

do you have an reference for this answer, Amar? – Martin Jul 05 '16 at 15:07
@Martin http://stackoverflow.com/questions/5809954/mysql-restrict-and-no-action#5810022 – Martin AJ Jul 05 '16 at 15:08
you can test this like this, [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name, ...) REFERENCES tbl_name (index_col_name,...) [ON DELETE reference_option] [ON UPDATE reference_option] reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION – Amar Srivastava Jul 05 '16 at 15:50

score 2 · Answer 3 · answered Feb 06 '20 at 06:50

2

RESTRICT will not allow to delete or update parent record if record existing in child table

answered Feb 06 '20 at 06:50

Saeed Ahmed

88
6

score -1 · Answer 4 · answered Jul 05 '16 at 15:52

you can test this like this,

                                                                                                                          [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name, ...)
REFERENCES tbl_name (index_col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]

reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION

What's the meaning of RESTRICT in Foreign Keys?

4 Answers4