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.

Sorry if this question is naive, but I was unable to understand possible usage of RESTRICT or No ACTION. Consider a simple database of

CREATE TABLE column1
(
first_id int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (first_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci";

CREATE TABLE column2
(
second_id int(11) NOT NULL AUTO_INCREMENT,
first_id int(11) REFERENCES column1(first_id) ON DELETE CASCADE,
PRIMARY KEY (second_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci";
  1. If DELETE FROM column1 WHERE first_id='XX' the corresponding row in the column2 (child column) will be deleted
  2. If DELETE FROM column2 WHERE first_id='XX' nothing will be deleted from column1 (parent column)

Right? then, what and how RESTRICT or NO ACTION can change this scenario ?

share|improve this question
add comment

2 Answers

up vote 5 down vote accepted

In MySQL, RESTRICT and NO ACTION are synonyms:

In MySQL, foreign key constraints are checked immediately, so NO ACTION is the same as RESTRICT. [src]

Now, you are asking how this affects a DELETE FROM column1 WHERE first_id='XX' if the table is defined like so:

CREATE TABLE `column2` (
 `second_id` int(11) NOT NULL AUTO_INCREMENT,
 `first_id` int(11) NOT NULL,
 PRIMARY KEY (`second_id`),
 KEY `fk_first_id` (`first_id`),
 CONSTRAINT `fk_first_id` FOREIGN KEY (`first_id`) REFERENCES `column1` (`first_id`)
) ENGINE=InnoDB;

Notice there is no ON DELETE CASCADE or ON UPDATE CASCADE clause. The manual specifically states that by leaving this off, you are implying a RESTRICT:

Specifying RESTRICT (or NO ACTION) is the same as omitting the ON DELETE or ON UPDATE clause.

So, now when the table is defined like this, a DELETE FROM column1 WHERE first_id='3' will fail if there are any rows in column2 that have a first_id value of 3.

If you had a child table that you needed to do some processing on prior to deleting the values, you might use this to ensure that the parent cannot be deleted until you have cleaned up the child table appropriately. It still offers protection against orphan rows in the child table (orphans are rows that exist in the child table, with no matching reference in the parent table).

share|improve this answer
    
Some points are vague to me: 1. You mean that whether using RESTRICT or omitting it in column1 is the same? then this is optional? 2. You talked about orphan children. In the present case, there should be NO orphan, as ON DELETE CASCADE should guarantee deleting the child upon deleting the parent. Am I right? –  All Feb 21 '12 at 15:24
    
In your example, you are right. Deleting from parent will delete the children (no orphans). "You mean that whether using RESTRICT or omitting it in column1 is the same" ON DELETE RESTRICT is the same as omitting ON DELETE all together. –  Derek Downey Feb 21 '12 at 15:29
add comment

Both your assumptions are correct. CASCADES flow from the 1 (column1) to the many (colummn2) side.

NO ACTION and RESTRICT do the same thing: they would prevent the delete from column1 in this case. The column2 delete would be allowed because it is on the many side

share|improve this answer
add comment

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.