Error when copying tables with foreign keys

lemon_juice's profile image lemon_juice posted 14 years ago in General Permalink
When I try to copy a table by selecting Create New -> Copy Table, I get this error:

SQL Error (1005): Can't create table 'xxx' (errno: 121) Duplicate key on write or update

It looks like heidisql puts identical foreign key names in the generated CREATE TABLE sql but they should be unique within a database. When I copy the sql from the log and change the FK names to something unique the query is executed. This happens on build 3424.
ansgar's profile image ansgar posted 14 years ago Permalink
Foreign key names have to be unique in a database? Is that true? Didn't knew that, interesting stuff. That would explain the whole bunch of "Duplicate key..." errors I got while debugging the table editor.

Regarding the "Copy table" dialog - should there be some random key name generator or how could that be solved?
lemon_juice's profile image lemon_juice posted 14 years ago Permalink
Well, technically speaking it's not FK name but a *symbol*, this is what's between the CONSTRAINT and FOREIGN KEY keywords. Here are the specs:

http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

They say:

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

So I think the best solution would be to remove the symbol name altogether from the CREATE TABLE code, then the db will create one automatically. When no symbol is given then the names end up like this:

mytable_ibfk_1
mytable_ibfk_2
etc.
Code modification/commit from ansgar.becker, 14 years ago, revision 5.1.0.3430
Foreign key constraint symbol names must be unique in a database. To avoid violation errors the "Copy table" dialog should just leave the CONSTRAINT clause out from the CREATE TABLE code. In that case MySQL auto creates a valid name on demand. See http://www.heidisql.com/forum.php?t=6086
ansgar's profile image ansgar posted 14 years ago Permalink
Solved in r3430 - at least for the "Copy table" dialog. Thanks for the enlightenment!
lemon_juice's profile image lemon_juice posted 14 years ago Permalink
Works very well now! Thanks for the quick update!

Please login to leave a reply, or register at first.