Donkey Trouble Donkey Trouble - 2 months ago 9
MySQL Question

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

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.

Answer

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.

Comments