Setting up foreign key in Mysql Workbench

Question

I am trying to set up a foreign key in Mysql workbench. I used the same name for the foreign key as the primary key of the table I am trying to set a relationship with. I already have one relation set up this way in another table, but When I try and apply the alterations to this table, the script gives me an error:

Error 1005: Can't create table 'X.#sql-718_a' (errno: 121)

SQL Statement:

ALTER TABLE `X`.`X_use`    
ADD CONSTRAINT `XyzID`   
FOREIGN KEY (`XyzID` )   REFERENCES `X`.`Xyz` (`XyzID` )   
ON DELETE NO ACTION   O
N UPDATE NO ACTION , 
ADD INDEX `XyzID` (`XyzID` ASC)  , 

However, if I change the foreign key name to "AbcID" I have no problem setting up the foreign key relation. Why is that and why can't I have the primary key name from one table be the same for the foreign key for this table? I have set up relations like that previously but for this table I cannot.

Solution

Constraint names have to be unique within the database.

That (errno: 121) in the error message means that MySQL encountered a duplicate key exception. The usual cause of this is that there is already a constraint of the same name in the database.

This "unique name" requirement is one reason why the normative pattern is to include the table name when constructing the name of the foreign key constraint. e.g. FK_table_cols e.g. FK_X_use_XyzID.

Why must the constraint name be unique within the database? That's a question for the dbms designers.

But consider this: when the database encounters a constraint violation, it throws an error that contains the name of the constraint. When that constraint name references only one constraint in the database, that makes locating the problem a bit easier.

Comments

[BK435]

I guess, I should go back and look over ACID properties of DB thoroughly again. Thanks!

[spencer7593]

I don't believe the ACID properties of a database specify whether or not names of foreign key constraints should be (or need to be) unique. Certainly, there needs to be some sort of unique identifier, but that could just as easily be a combination of table_name/partition_name/constraint_name, without violating the A,C,I or D properties. (Then again, I may need to be re-schooled on this.)