Adding foreign key to existing table gives error 1

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

So a team member figured this out. The one table was set with the type utf8_general, and another was set to the type default. I didn't think this was an issue, since the default is utf8_general, but apparently mysql just looks at the type names and not the underlying type.

Answer 2

When using MysqlWorkbench the error is misleading. My issue was that I was trying to add a foreign key constraint on table that already had rows and one of the rows was empty (did not meet the FK constraint. Instead of complaining that constraint will fail if applied, MysqlWorkbench reported that table exists.

Removing the offending row fixed (or adding and constraint acceptable value to the field) solved the problem.

Answer 3

• Check the Storage Engine type for CustomizationSet table.

I had a same issue but i could solve it by changing engine type to InnoDB , because few types don't support foreign key constraints.