Error when copying tables with foreign keys
| User, date | Message |
|---|---|
|
Written by lemon_juice
3 years ago Category: General 94 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
3 years ago 3949 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
3 years ago 94 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
3 years ago 3949 posts since Fri, 07 Apr 06 |
Solved in r3430 - at least for the "Copy table" dialog. Thanks for the enlightenment! |
|
Written by lemon_juice
3 years ago 94 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. |