Search code examples
mysqlsqldatabaseforeign-keyscascading-deletes

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?

Solution

  • 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.