MySQL - Adding Foreign Key To Existing Table

Question

I'm trying to add a foreign key to the following table, so when a row is deleted from it's parent table 'accounts', the corresponding rows in 'threads' are deleted. I've already done this successfully with the 'messages' table, which is a child of 'threads'.

ALTER TABLE  `threads` ADD FOREIGN KEY (  `owner_id` ) 
REFERENCES `social_network`.`accounts` (`id`) 
ON DELETE CASCADE

Now though, I'm getting the error:

#1050 - Table '.\social_network\threads' already exists

Which doesn't make sense to me, because I wouldn't be trying to 'ALTER TABLE' if it didn't already exist. To add more confusion, this SQL was generated automatically by phpMyAdmin using it's built in tools.

Can someone explain to me what causes this error to be thrown and how to fix it?

This erronous message most often means, that the foreign key can not be added due to a preexisting key violation. — Eugen Rieck, Jan 29 '14 at 03:03
I haven't added any foreign keys to this table. There is a child table which has a foreign key referencing this table though, could this be causing the issue? Is there a way (using phpMyAdmin or otherwise) to see a list of foreign keys for a specified table? — Nathan Wiles, Jan 29 '14 at 03:07
What I ment is: At the time of creating the foreign key, if a key violation exists, the key obviously can't be created - this sometimes leads to this strange "table already exists" message. Check for rows, that would violate the new key. — Eugen Rieck, Jan 29 '14 at 03:09
I'm not sure what you mean exactly by a key violation. Do you mean columns which are configured erroneously? Or rows which contain data that violates the constraints of their column? — Nathan Wiles, Jan 29 '14 at 03:15
The latter: You create the foreign key, when there already is data in the tables. So it is possible, that some of this data would not be "allowed", if the foreign key is in place. This means, creating the foreign key must fail. It is a known bug in some versions of MySQL, that sometimes the error message shown is "table already exists", which is nonsensical. — Eugen Rieck, Jan 29 '14 at 03:21
Just try "SELECT * FROM threads LEFT JOIN accounts ON threads.owner_id=accounts.id WHERE accounts.id IS NULL" - if this doesn't come up empty, you have this problem. — Eugen Rieck, Jan 29 '14 at 03:24
In which case you have a different problem, than the one I thought it might be - sorry to bother you. — Eugen Rieck, Jan 29 '14 at 03:30
What db engine are you using? Did you check the answers in this question? http://stackoverflow.com/q/7019018/1507998 — rsanchez, Feb 04 '14 at 21:18
I understand that I need to use the InnoDB engine for use with foreign keys, that's what I've been using. — Nathan Wiles, Feb 05 '14 at 01:49

score 3 · Answer 1 · answered Feb 04 '14 at 03:17

3

I think what you can do are as follows:

Check if table exists DROP TABLE IF EXISTS
REPAIR TABLE
Try Inserting values, it should work.

answered Feb 04 '14 at 03:17

Maverick

1,167
1
8
16

The table most certainly exists, I'm able to perform other operations on it. I did attempt to use the repair statement on the table. The query executed successfully, but I received the message : The storage engine for the table doesn't support repair. I'm using the InnoDB engine, which as far as I know is necessary if I want to implement foreign keys. – Nathan Wiles Feb 05 '14 at 01:48
Even though the repair operation wasn't supported, you knew what the issue was and were the first to offer a good solution, so I gave you the bounty. Thanks! – Nathan Wiles Feb 08 '14 at 10:23

score 2 · Answer 2 · answered Feb 06 '14 at 16:12

Have you seen this link ? Link.

He talks about Rapairing tables with InnoDB. I guess you drop the table and create a new, now with the foreign key. I doing this a lot here! You can see this link too...

And you've check if all your tables have the same engine ?

score 1 · Accepted Answer · answered Feb 08 '14 at 10:18

This answer is a followup to Bruno Casali's and Maverick's posted answers. I was in fact able to fix this by repairing my table. The InnoDB engine doesn't support the REPAIR operation, so I just recreated and repopulated the table:

CREATE TABLE threads_tmp LIKE threads;
INSERT INTO threads_tmp SELECT * FROM threads;
TRUNCATE TABLE threads;
INSERT INTO threads SELECT * FROM threads_tmp;
DROP TABLE threads_tmp;

Hope this helps anyone having the same issue.

MySQL - Adding Foreign Key To Existing Table

3 Answers3