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.

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?

share|improve this question
1  
now that we got the typo out of the way, I think this question is great as it highlights a potential mysql gotcha –  Derek Downey Feb 28 '12 at 19:54
add comment

2 Answers

up vote 2 down vote accepted

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$$
share|improve this answer
    
vivid clarification of the issue! Thanks :) –  All Feb 28 '12 at 23:08
add comment

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.

share|improve this answer
1  
The missing type was typo in writing here. The types are all the same in parent, child1, and child2. Yes, I also think that I must use the secure syntax of child2; but still curious to know what the first one does not work ;) –  All Feb 28 '12 at 19:36
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.