Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 4 down vote accepted

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.

share|improve this answer

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.

See also this related qestion: What is the purpose of SET NULL in Delete/Update Foreign Keys constraints?

By defining a foreign key, you've told the database to not accept entries in the child table that do not have a corresponding value in the parent.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.