Strange foreign key issue with MySQL 5.5.9

Question

I'm experiencing a rather strange problem with foreign keys, namely this doesn't work as expected:

ALTER TABLE contact_contactlist 
  ADD FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE;

ALTER TABLE contact_contactlist 
  ADD FOREIGN KEY (contactlist_id) REFERENCES contactLists(id) ON DELETE CASCADE;

ALTER TABLE contactLists 
  ADD FOREIGN KEY (owner_id) REFERENCES serviceAccounts(id);

(auto-generated by Doctrine 2)

Yet, this works perfectly:

ALTER TABLE `contact_contactlist`
  ADD CONSTRAINT `contact_contactlist_ibfk_5` 
    FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `contact_contactlist_ibfk_6` 
    FOREIGN KEY (`contactlist_id`) REFERENCES `contactLists` (`id`) ON DELETE CASCADE;  
ALTER TABLE `contactLists`
  ADD CONSTRAINT `contactlists_ibfk_1` 
    FOREIGN KEY (`owner_id`) REFERENCES `serviceAccounts` (`id`);

(phpMyAdmin export of the very same keys above)

The problem manifests itself as MySQL won't let me insert anything into the tables if the keys are defined with the SQL from the first block, complaining on referential integrity (even though the referenced entries exist), yet all is well if I set the keys with the SQL from the second block.

I am aware that I can just "go on and be happy" using the latter key definition SQL, but I'd prefer if I could somehow just stick to the Doctrine-generated SQL as I'm still early in development and model will change quite often (thus the two-step manual key fixing will become rather annoying).

EDIT

Here are the create statements

CREATE TABLE contact_contactlist (
  contact_id BIGINT NOT NULL, 
  contactlist_id BIGINT NOT NULL, 
  INDEX contact_contactlist_contact_id_idx (contact_id), 
  INDEX contact_contactlist_contactlist_id_idx (contactlist_id), 
  PRIMARY KEY(contact_id, contactlist_id)) 
ENGINE = InnoDB; 

CREATE TABLE contactLists (
  id BIGINT AUTO_INCREMENT NOT NULL, 
  owner_id INT DEFAULT NULL, 
  name VARCHAR(255) NOT NULL, 
  INDEX contactLists_owner_id_idx (owner_id), 
  PRIMARY KEY(id)) 
ENGINE = InnoDB;

Which engine are you using for mysql(mostly innoDB?) and what is the error message in the first set? Do the foreign keys even get created? — Jai, Mar 17 '11 at 12:17
@Jai I'm using InnoDB, keys do get created and the error is "Cannot add or update a child row: a foreign key constraint fails (`prosend`.`contact_contactlist`, CONSTRAINT `contact_contactlist_ibfk_4` FOREIGN KEY (`contactlist_id`) REFERENCES `contactLists` (`id`) ON DELETE CASCADE)" even though I'm trying to add a value that **does exist** in contactLists.id — Dachaz, Mar 17 '11 at 13:06
Can you post the Show Create Table output in both cases? I am wondering if the index issue for foreign keys still exists in 5.5.+. (You should see indexes for the constraints with the same name too..wondering if that is not created in the first) — Jai, Mar 17 '11 at 13:23
Ref this http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html . It FK issue doesnt exist in 5.5+, but u may be able to detect if something wrong is happening. — Jai, Mar 17 '11 at 13:31
`CREATE TABLE contact_contactlist (contact_id BIGINT NOT NULL, contactlist_id BIGINT NOT NULL, INDEX contact_contactlist_contact_id_idx (contact_id), INDEX contact_contactlist_contactlist_id_idx (contactlist_id), PRIMARY KEY(contact_id, contactlist_id)) ENGINE = InnoDB; CREATE TABLE contactLists (id BIGINT AUTO_INCREMENT NOT NULL, owner_id INT DEFAULT NULL, name VARCHAR(255) NOT NULL, INDEX contactLists_owner_id_idx (owner_id), PRIMARY KEY(id)) ENGINE = InnoDB;` - will check your link for reference. — Dachaz, Mar 17 '11 at 14:10
I can't speak to your specific issue, but I would like to say that it is bad practice to add foreign keys without explicitly naming them. The primary problem with that is that you could end up with multiple redundant foreign keys if the statement runs more than once. — Ike Walker, Mar 17 '11 at 15:22

score 1 · Accepted Answer · edited May 23 '17 at 11:48

1

If you're using Mac OS X, you might be running into a bug in MySQL:
MySQL 5.5 foreign key constraint fails when foreign key exists

edited May 23 '17 at 11:48

Community

1
1

answered Jun 07 '11 at 07:36

penfold

1,523
1
14
21

Yup, I realised this never happens on the Linux server I'm publishing to, so it must be that. – Dachaz Jun 08 '11 at 04:04

Strange foreign key issue with MySQL 5.5.9

1 Answers1