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

score 11 · Answer 1 · answered Oct 24 '11 at 08:22

11

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

answered Oct 24 '11 at 08:22

pjvr

121
1
3

2

You don't have to. My problem was that I did and after doing some reading I realized that I was trying to add an already existing constraint. Removing the constraint makes MySQL assign a name itself. This is from http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html: "If the CONSTRAINT symbol clause is given, the symbol value must be unique in the database. If the clause is not given, InnoDB creates the name automatically." – Nicsoft Feb 03 '12 at 09:48
2

This actually worked for me. Explicitly giving the constraint a name worked. Strange, because this table had no existing constraints. – Richard Nov 14 '13 at 12:42
1

Worked for me when I specified a name for the constraint. It must have been conflicting with another existing constraint. – Jimbali Dec 28 '13 at 16:15
1

Worked for me as well using phpMyAdmin, strange enough since the referred table didn't have any other constraints – Luca Giorgi Nov 30 '15 at 13:39
2

If anyone could explain to me why this work, I'd be glad to hear it. It fixed the issue but I have no idea why – La masse Mar 18 '20 at 09:55
This solution worked for me. However, if I did not want to specify the constraint to workaround this problem, then I needed to drop all the pre-existing constraints in the table `ALTER TABLE Registration DROP FOREIGN KEY MyConstraintName`..., and then add the foreign key `ALTER TABLE Registration ADD FOREIGN KEY...` – Arya Dec 03 '20 at 03:46

score 2 · Answer 2 · answered Jun 15 '12 at 18:09

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

score 2 · Accepted Answer · answered Jun 29 '12 at 18:38

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.

score 1 · Answer 4 · answered May 02 '13 at 01:00

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!

score 1 · Answer 5 · answered Oct 03 '13 at 06:57

1

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.

answered Oct 03 '13 at 06:57

Santosh

83
2
12

score 0 · Answer 6 · answered Jan 20 '14 at 14:03

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

score 0 · Answer 7 · answered Oct 31 '17 at 21:46

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.

score 0 · Answer 8 · edited Aug 15 '11 at 17:01

0

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

edited Aug 15 '11 at 17:01

avaleske

1,793
5
16
26

answered Aug 10 '11 at 23:31

ace

7,293
3
23
28

That returns the same error. Other foreign keys on other tables I've created with that syntax have worked, it just adds a default name for the key. – avaleske Aug 11 '11 at 16:02
Hmmm. It seems that it has something to do `1050 Table '.\dbname\registration'` and not the foreign key. Does adding another foreign key to another table works fine? – ace Aug 11 '11 at 16:42

score 0 · Answer 9 · answered Aug 10 '11 at 23:34

0

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.

answered Aug 10 '11 at 23:34

invertedSpear

10,864
5
39
77

1

They are both char(36), but the one in Registration is nullable. Would that make a difference? – avaleske Aug 11 '11 at 16:07

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

9 Answers9

Linked