distal-attribute
distal-attribute
distal-attribute
distal-attribute

Error when copying tables with foreign keys

User, date Message
Written by lemon_juice
4 years ago
Category: General
127 posts since Tue, 29 Jun 10
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.
Written by ansgar
4 years ago
4988 posts since Fri, 07 Apr 06
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?
Written by lemon_juice
4 years ago
127 posts since Tue, 29 Jun 10
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.
Written by ansgar
4 years ago
4988 posts since Fri, 07 Apr 06
Solved in r3430 - at least for the "Copy table" dialog. Thanks for the enlightenment!
Written by lemon_juice
4 years ago
127 posts since Tue, 29 Jun 10
Works very well now! Thanks for the quick update!
 

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