Error when copying tables with foreign keys

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.

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?

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.

Solved in r3430 - at least for the "Copy table" dialog. Thanks for the enlightenment!

Works very well now! Thanks for the quick update!