Usage of RESTRICT or No ACTION in FOREIGN KEY Constraints

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";

• If DELETE FROM column1 WHERE first_id='XX' the corresponding row in the column2 (child column) will be deleted

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

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

StackExchange Database Administrators Q#13571, answer score: 6