About foreign key on mysql
I'm migrating an old Sybase database to MySQL and I have to create foreign keys. Now, since Sybase exports its data, I've tried to use its query as it is on MySQL:
ALTER TABLE Mosaico.fornit_col ADD FOREIGN KEY fkey_fco_fornit_maz ( fco_idformaz ) REFERENCES Mosaico.fornit_maz ( fma_id ); -- ALTER TABLE Mosaico.fornit_col ADD FOREIGN KEY fkey_fco_mater_col ( fco_idmatcol ) REFERENCES Mosaico.mater_col ( mco_id ); --
and seems to works good. If I execute them on MySQL Admin Console, the queries work, but on Workbench, the second one gives this error:
ALTER TABLE Mosaico.fornit_col ADD FOREIGN KEY fkey_fco_mater_col ( fco_idmatcol ) REFERENCES Mosaico.mater_col ( mco_id ) Error Code: 1050. Table '.\mosaico\fornit_col' already exists
What's the problem?
Check out the bug report here:
In the end, the people with the problem upgraded their server and it fixed the issue. From reading it, I'm not sure that there isn't a bug in there somewhere. They did have some workarounds like putting in constraint names/changing them. Try some of their fixes. If you think this is the same, and you can repeat the error, 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