Bug #10744 Adding two constraints with the same name.
Submitted: 19 May 2005 13:45 Modified: 13 May 2010 16:04
Reporter: Kai Ruhnau Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:4.1.11, 5.0.6 OS:Linux (Gentoo Linux)
Assigned to: Assigned Account CPU Architecture:Any
Triage: Triaged: D5 (Feature request)

[19 May 2005 13:45] Kai Ruhnau
Description:
Adding two FOREIGN KEY Constraints with the same name in one database results in a very cryptic error message:

ERROR 1005 (HY000): Can't create table './test/#sql-5e04_1443.frm' (errno: 121)

# perror 121
OS error code 121:  Remote I/O error
MySQL error code 121: Duplicate key on write or update

How to repeat:
create table fk_test1 ( ID int(10) UNSIGNED NOT NULL auto_increment, ID_fk INT(10) UNSIGNED NOT NULL, KEY (ID_fk), PRIMARY KEY (ID)) ENGINE=InnoDB;
create table fk_test2 (ID int(10) UNSIGNED NOT NULL auto_increment, ID_fk int(10) unsigned NOT NULL, KEY (ID_fk), PRIMARY KEY (ID)) ENGINE=InnoDB;
ALTER TABLE fk_test1 ADD CONSTRAINT my_constraint FOREIGN KEY (ID_fk) REFERENCES fk_test2 (ID) ON DELETE CASCADE;
ALTER TABLE fk_test2 ADD CONSTRAINT my_constraint FOREIGN KEY (ID_fk) REFERENCES fk_test1 (ID) ON DELETE CASCADE;

=> ERROR 1005 (HY000): Can't create table './test/#sql-5e04_1443.frm' (errno: 121)

Suggested fix:
It would be nice to see something like "CONSTRAINT my_constraint already exists in fk_test1"
[19 May 2005 13:51] Kai Ruhnau
Using the automatic generated names for CONSTRAINTS in another table obscures this even more.

create table fk_test1 ( ID int(10) UNSIGNED NOT NULL auto_increment, ID_fk INT(10) UNSIGNED NOT NULL, KEY (ID_fk), PRIMARY KEY (ID)) ENGINE=InnoDB; 
create table fk_test2 (ID int(10) UNSIGNED NOT NULL auto_increment, ID_fk int(10) unsigned NOT NULL, KEY (ID_fk), PRIMARY KEY (ID)) ENGINE=InnoDB; 

#Use an automatic generated name
ALTER TABLE fk_test1 ADD CONSTRAINT fk_test2_ibfk_1 FOREIGN KEY (ID_fk) REFERENCES fk_test2 (ID) ON DELETE CASCADE; 

#Don't give a name at all
ALTER TABLE fk_test2 ADD FOREIGN KEY (ID_fk) REFERENCES fk_test1 (ID) ON DELETE CASCADE;
[20 May 2005 6:28] Jan Lindström
Better error messages for a foreign key errors are in the TODO.

Regards,
    JanL
[20 May 2005 11:39] Heikki Tuuri
Hi!

I think

SHOW INNODB STATUS

prints a more detailed error message.

Regards,

Heikki
[21 May 2005 6:26] Heikki Tuuri
Jan,

please modify 5.1 so that we can pass a sensible error message from ::create(). Currently, MySQL seems to treat errors as 'engine errors'.

You should add a suitable error message to /sql/share/..., and transmit it to the client in this case.

Check dict0dict.c, and add sensible error messages to MySQL also for other common user errors in foreign keys. A missing index in the referenced table is common, as well as a wrong data type in the referenced column.

Regards,

Heikki
[26 Oct 2006 11:39] Jacques
I've also bumped into this on MySQL 5.0.26.
[10 Feb 2007 15:25] Vincent van Ederen
I also had this problem, and removed the 'FK_' prefix from the foreign keys.
This took care of the problem.