MySQL Bugs: #10744: Adding two constraints with the same name.

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

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"

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;

Better error messages for a foreign key errors are in the TODO.

Regards,
    JanL

Hi!

I think

SHOW INNODB STATUS

prints a more detailed error message.

Regards,

Heikki

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

I've also bumped into this on MySQL 5.0.26.

I also had this problem, and removed the 'FK_' prefix from the foreign keys.
This took care of the problem.