Take the 2-minute tour ×
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.

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

2 Answers 2

up vote 1 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
    
You are absolutely right! It was my misunderstanding :( It seems there is no way to delete parent and keep orphan child when connection is by FK. –  All Feb 29 '12 at 21:28
    
In theory, you could probably do something like SET FOREIGN_KEY_CHECKS=0; and then delete the parent. Why would you want to do that ? –  RolandoMySQLDBA Feb 29 '12 at 21:33
    
For example, we have profile for each city; when we delete a city profile, we do not want to delete it from Map too. We keep the city on the Map but without profile (which is in table City). –  All Feb 29 '12 at 21:39
    
@Ali then split city_profile into a different table that ALSO references the city table. –  Derek Downey Mar 1 '12 at 1:24

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.

share|improve this answer
    
See this related question. –  Derek Downey Feb 29 '12 at 22:41
    
Hey, nice answer and nice related question. +1 !!! –  RolandoMySQLDBA Feb 29 '12 at 22:44
    
but this will delete the corresponding value in child table (set it to be NULL); thus missing it! –  All Feb 29 '12 at 23:20
1  
@Ali By defining your Foreign Key you've told the database to not accept entries in child that do not have a corresponding value in parent. Why would you want to break that relation? –  Derek Downey Mar 1 '12 at 1:22
    
good point! apparently I must obey ;) –  All Mar 1 '12 at 1:52

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.