shank shank - 2 months ago 17
MySQL Question

2 foreign key references for a single primary key

I have 3 tables: agreement, user, emails as follows and the id in user table is acting as a foreign key in email table(id(FK)) as well as agreement table id_two(FK).

enter image description here

I have referenced id(Foriegn Key) in emails table to id(Primary key) in user table using the below code successfully:



ALTER TABLE emails MODIFY COLUMN id INT NOT NULL,
ADD CONSTRAINT id
FOREIGN KEY(id)
REFERENCES user(id);





But i am getting an error #1022 - Can't write; duplicate key in table '#sql-13f0_30e'

when i execute the below query to refer id_two of agreement table to id(PK) of user table.



ALTER TABLE agreement MODIFY COLUMN id_two INT NOT NULL,
ADD CONSTRAINT id_two
FOREIGN KEY(id_two)
REFERENCES user(id);




Answer

That's most probably because you already have a constraint defined in agreement table named id_two. Change the name of the constraint and see

ALTER TABLE agreement MODIFY COLUMN id_two INT NOT NULL,
 ADD CONSTRAINT id_FK2
 FOREIGN KEY(id_two)
 REFERENCES user(id);