Using Foreign Key and References for ON DELETE CASCADE in mysql

Question

I was thinking that these two ways for creating foreign keys are the same

CREATE TABLE child1 (
id int(11) not null auto_increment,
parent_id int(11) REFERENCES parent_table(parent_id) ON DELETE CASCADE,
PRIMARY KEY(id)
);

and

CREATE TABLE child2 (
id int(11) not null auto_increment,
parent_id int(11),
PRIMARY KEY(id),
FOREIGN KEY(parent_id) REFERENCES parent_table(parent_id) ON DELETE CASCADE
);

but when deleting a record from parent table, the corresponding record in table child2 will be deleted but NOT that of table child1.

Where am I wrong? REFERENCES is not enough and we necessary need to write FOREIGN KEY to use ON DELETE CASCADE?

Answer 1

It is a MySQL "gotcha". The first way does NOT work.

From MySQL docs, FOREIGN KEY Constraints:

Important:

... 4 paragraphs below...

Furthermore, InnoDB does not recognize or support “inline REFERENCES specifications” (as defined in the SQL standard) where the references are defined as part of the column specification. InnoDB accepts REFERENCES clauses only when specified as part of a separate FOREIGN KEY specification. For other storage engines, MySQL Server parses and ignores foreign key specifications.

Creating the 2 tables (way 1):

CREATE TABLE parent_table (
parent_id int(11) not null auto_increment,
PRIMARY KEY(parent_id)
);

CREATE TABLE child1 (
id int(11) not null auto_increment,
parent_id int(11) REFERENCES parent_table(parent_id) ON DELETE CASCADE,
PRIMARY KEY(id)
);

Lets see what is child1:

> SHOW CREATE TABLE child1 ;

delimiter $$

CREATE TABLE `child1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$

Where is the FOREIGN KEY ? ... Gone with the wind (and without warning)

---

Creating the table child2 (way 2) works fine:

CREATE TABLE child2 (
id int(11) not null auto_increment,
parent_id int(11),
PRIMARY KEY(id),
FOREIGN KEY(parent_id) REFERENCES parent_table(parent_id) ON DELETE CASCADE
);

> SHOW CREATE TABLE child2 ;

delimiter $$

CREATE TABLE `child2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `parent_id` (`parent_id`),
  CONSTRAINT `child2_ibfk_1` 
    FOREIGN KEY (`parent_id`) REFERENCES `parent_table` (`parent_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$

Answer 2

Interesting. I didn't think the syntax for child1 would even pass when I first looked at this.

One possible reason child1 fails that you're not explicitly providing a type. Foreign keys need to match type.

Corresponding columns in the foreign key and the referenced key must have similar internal data types inside InnoDB so that they can be compared without a type conversion. The size and sign of integer types must be the same. The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must be the same. [src]

I doubt that's the reason since your DDL is minimalistic and the parent_id in the parent table is probably similarly defined.

But disregarding everything else about this question, you should use the syntax of child2 simply because it's more clear and more maintainable.