Reputation: 1853
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...
Upvotes: 24
Views: 21825
Reputation: 1004
Short: I ran into this error due to the following reason: "Foreign key names must be unique within the whole database"
If you do not give a name for the foreign key, the auto-generated name runs into the same issue.
Background: During my fixing activities I found this, which gave me the fixing hint: Duplicate key on write or update?
In that past I did fight against that foreign key constraint and thus had different "test tables" within the same database. One had the foreign key with exaclty the same name and thus that name was blocked. Dropping my test tables solved it. Renaming the key might have done it too.
Upvotes: 0
Reputation: 495
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.
Upvotes: 0
Reputation: 127
I had a similar problem and in the end it was a problem of Integrity Constraint. The Foreign Key column was referencing a foreign column that didnt exist.
Try run the following to test whether this is the case:
select r.customization_set_guid, c.customization_set_guid
from Registration r
right join CustomizationSet c
on
r.customization_set_guid = c.customization_set_guid
where isnull(c.customization_set_guid);
Upvotes: 0
Reputation: 83
- 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.
Upvotes: 1
Reputation: 12301
I got the same error, and since my case wasnt mentioned yet, i ll post this answer and hopefully it may save somebody's time!
My two tables engines, where different. The one was InnoDB, and the other MyIsam.
To change the engine of a table:
choose table, hit alter table, and then to hit that double arrow at the right-most of the Workbench(so it will point upwards).
Now change the engine!
Upvotes: 1
Reputation: 1853
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.
Upvotes: 2
Reputation: 2235
It looks like there is a bug report for this at MySQL located here:
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
Upvotes: 2
Reputation: 131
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);
Upvotes: 12
Reputation: 7603
I'm not sure if it's a typo but shouldn't be
ALTER TABLE Registration ADD FOREIGN KEY
(
customization_set_guid
) REFERENCES CustomizationSet (
customization_set_guid
);
be something like
ALTER TABLE Registration ADD FOREIGN KEY
customization_set_guid_fk (customization_set_guid)
REFERENCES CustomizationSet (customization_set_guid);
Upvotes: 0
Reputation: 11054
Not sure about the table already existing, but the reason it's not letting you choose the column you want is most likely due to the columns not being the same type. Check to ensure they are both the same type, same length, and have all the same options.
Upvotes: 0