CODEWITHSUNDEEP
mysqlsqldatabaseforeign-keyscascading-deletes
Nathan Wiles
Nathan Wiles

Reputation: 926

MySQL - Adding Foreign Key To Existing Table

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?

Upvotes: 1

Views: 1089

Answers (3)

Nathan Wiles
Nathan Wiles

Reputation: 926

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.

Upvotes: 1

Bruno Casali
Bruno Casali

Reputation: 1380

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 ?

Upvotes: 2

Maverick
Maverick

Reputation: 1185

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.

Upvotes: 3

Related Questions