Adding foreign key to existing table gives error 1050 table already exists

Question

I've a table CustomizationSet with the columns:

customization_set_guid (which is a non-nullable guid and also the primary key) creator_account_guid and a few others 

And a table with existing data Registration with the columns:

registration_id (an int and the primary key) customization_set_guid (also a guid (so a char(36)) which is nullable, and all entries are currently null) and a few other columns 

When I try and run

ALTER TABLE Registration ADD FOREIGN KEY      (         customization_set_guid     ) REFERENCES CustomizationSet (         customization_set_guid     ); 

in MySQL Workbench, it gives the error 1050Table '.\dbname\registration' already exists.

If I try to use the UI to add the foreign keys with the Foreign Keys tab of the Alter Table Dialog, and choose CustomizationSet as the referenced table, it doesn't let me choose customization_set_guid in the list of columns.

I'm really not sure why it won't let me add this foreign key. I've just successfully created foreign keys between tables I just added. The Registration table has existed for awhile...

Answer 1

I got the same error, and it was due to the fact that the foreign key already existed. What you want is just to add the constraint:

ALTER TABLE Registration    ADD CONSTRAINT idx_Registration_CustomizationSet    FOREIGN KEY (customization_set_guid)    REFERENCES CustomizationSet(customization_set_guid); 

Answer 2

It looks like there is a bug report for this at MySQL located here:

MySQL Bug 55296

In the end, I guess they upgraded their server and it fixed the issue. From reading it, I'm not sure though. They did have some workarounds like putting in constraint names/changing them. If you think this is the same, I would request that the bug is reopened.

At one point, they mention the types didn't match and workbench was responding with the wrong error (it should have been an errno 150, or errno 121). You can see the causes for those errors here: MySQL Foreign Key Errors and Errno 150