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?

Answer 1

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

Answer 2

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.