Exact Meaning of MySQL's Foreign Key 'on delete restrict' Clause

Question

I have two MySQL tables: collections and privacy_level.
I define them with a foreign key relationship as such:

CREATE TABLE collections (
  coll_id smallint NOT NULL AUTO_INCREMENT UNSIGNED,
  name varchar(30) NOT NULL,
  privacy tinyint NOT NULL UNSIGNED DEFAULT '0',
  PRIMARY KEY(coll_id),
  INDEX(privacy),
  FOREIGN KEY fk_priv (privacy) REFERENCES privacy_level (level) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB;  

 CREATE TABLE privacy_level (
   level tinyint NOT NULL UNSIGNED,
   name varchar(20) NOT NULL,
   PRIMARY KEY (level)
 ) ENGINE InnoDB;

My question is about the ON DELETE RESTRICT clause and I couldn't derive the answer from the online manual or a google search.

Does this mean that I can never delete a row from privacy_level?
Or, does it mean that I can't delete a row from privacy_level if a row from collections.privacy has a value that is the same as a value in privacy_level.level?

That is, if privacy_level has level = 2, name = 'top secret' but no entry in collections.Privacy has privacy = 2, can I delete the level = 2, name = 'top secret' entry? Or is it forbidden on a column wide basis?

Thanks for any insight.

score 141 · Accepted Answer · edited Sep 19 '16 at 16:16

141

ON DELETE RESTRICT means you can't delete a given parent row if a child row exists that references the value for that parent row. If the parent row has no referencing child rows, then you can delete that parent row.

ON DELETE RESTRICT is pretty much superfluous syntax, because this is the default behavior for a foreign key anyway.

edited Sep 19 '16 at 16:16

Hari Harker

702
1
12
29

answered Nov 09 '11 at 23:19

Bill Karwin

538,548
86
673
828

2

Do you mean `ON DELETE RESTRICT` is the same as `ON DELETE NO ACTION`? – Shafizadeh Sep 05 '16 at 22:37
@Shafizadeh, read http://www.vertabelo.com/blog/technical-articles/on-delete-restrict-vs-on-delete-no-action – Bill Karwin Sep 06 '16 at 06:36
2

@Bill, that article says NO ACTION is the same as RESTRICT in MySQL, and it refers [back to SO](https://stackoverflow.com/questions/5809954/mysql-restrict-and-no-action) to back their statement... – Code4R7 May 23 '17 at 08:19
3

@Code4R7 cf."Specifying RESTRICT (or NO ACTION) is the same as omitting the ON DELETE or ON UPDATE clause." https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html – Bill Karwin May 23 '17 at 19:57

score 6 · Answer 2 · edited Aug 20 '16 at 04:30

6

Also you can use ON DELETE CASCADE, that means when you delete the parent all the children will be removed automatically, this is useful when you have a table associated with another that contains some parameters or settings.

edited Aug 20 '16 at 04:30

zinking

5,561
5
49
81

answered Aug 19 '16 at 19:38

jose miguel rivera rodríguez

473
4
5

2

which one is the parent and which one is the child row? Is it the one referencing another the child or the other way around? – Sebastián Grignoli Sep 26 '19 at 19:58
2

If you have two tables, users, and user_settings, the users' table is the parent, and the user settings table is the child. So, when you delete a user, automatically will delete the settings related to the deleted user. – jose miguel rivera rodríguez Jul 21 '20 at 19:10
While it does make sense that the 'users' table is the parent table, technically there is nothing saying that it can't be the other way around. It just depends on which column references what column. The referencing column is the child, the referenced column the parent. – Tim Anthony Mar 08 '22 at 12:52

Exact Meaning of MySQL's Foreign Key 'on delete restrict' Clause

2 Answers2

Linked