How to use RESTRICT for Foreign Key in mysql?

Question

In the database structure of

  CREATE TABLE Country (
  name varchar(40) NOT NULL,
  PRIMARY KEY  (name)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE City (
  name varchar(40) NOT NULL,
  PRIMARY KEY  (name)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE Map (
  country varchar(40) NOT NULL,
  city varchar(100) NOT NULL,
  PRIMARY KEY  (country,city),
  FOREIGN KEY (country) REFERENCES Country (name) ON DELETE CASCADE,
  FOREIGN KEY (city) REFERENCES City (name) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I expect to delete parent from City by leaving the corresponding value in child intact by these three equal commands

  FOREIGN KEY (city) REFERENCES City (name) ON DELETE NO ACTION
  FOREIGN KEY (city) REFERENCES City (name) ON DELETE RESTRICT
  FOREIGN KEY (city) REFERENCES City (name)

But when using NO ACTION OR RESTRICT or omitting ON DELETE. MySQL does not allow me to delete from parent column with this error:

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails 
('test'.'Map', CONSTRAINT 'Map_ibfk_2' FOREIGN KEY ('city') REFERENCES 'City'('name')
 ON DELETE RESTRICT

Where am I wrong? Isn't it the responsibility of the SQL's NO ACTION to delete the parent and leave the child orphan?

Answer 1

According to the MySQL Documentation on DELETE RESTRICT

• 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.

As for NO ACTION

• NO ACTION: A keyword from standard SQL. In MySQL, equivalent to RESTRICT. InnoDB 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.

DELETE RESTRICT protects the parent from deletion, not the children.

Answer 2

If you are wanting to delete the parent and leave the child, then you're probably wanting the ON DELETE SET NULL option:

SET NULL: Delete or update the row from the parent table, and set the foreign key column or columns in the child table to NULL. Both ON DELETE SET NULL and ON UPDATE SET NULL clauses are supported.

If you specify a SET NULL action, make sure that you have not declared the columns in the child table as NOT NULL.

There's a lot of 'not' in that last sentence, so just make sure the parent_id can be NULL.