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

avaleske picture avaleske · Aug 11, 2011 · Viewed 17.7k times · Source

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

pjvr picture pjvr · Oct 24, 2011

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);