2 foreign key references for a single primary key

Question

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).

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);

The best bet is that the constraint name id_two already exists in that table — Drew, Aug 11 '15 at 13:00
See : http://stackoverflow.com/questions/18056786/error-1022-cant-write-duplicate-key-in-table — PaulF, Aug 11 '15 at 13:02

score 2 · Accepted Answer · answered Aug 11 '15 at 13:01

2

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);

answered Aug 11 '15 at 13:01

Rahul

76,197
13
71
125

Now its giving this error: #1452 - Cannot add or update a child row: a foreign key constraint fails (`nda`.`#sql-13f0_32a`, CONSTRAINT `id_FK2` FOREIGN KEY (`id_two`) REFERENCES `user` (`id`)) – shank Aug 11 '15 at 13:03
That's because you are trying to add/update a key value which doesn't present in users table. Make sure you insert in primary table first and then to child table. – Rahul Aug 11 '15 at 13:04

score 1 · Answer 2 · answered Aug 11 '15 at 13:31

Primary key is a column or group of columns that uniquely identify a row. Every table should have a primary key. And a table cannot have more than one primary key.

Foreign key is a column or set of columns in one table whose values must have matching values in the primary key of another (or the same) table. A foreign key is said to reference its primary key. Foreign keys are a mechanism for maintaining data integrity.

For your problem, I have created the script for you. I did it from scratch since I need to have table available before adding the constraints but I didn't added all columns, sorry!!:

--Create user table and add id as primary key

    CREATE TABLE user
    ( 
      Id Number (5) ,
      Username Varchar2 (25),
      Eamil Varchar2 (25),
       CONSTRAINT user_pk PRIMARY KEY (id)
    );

--Create "agreement" table and add "Agreement_Id" as primary key

    CREATE TABLE agreement
    ( 
      Id_Two Number (5) ,
      Agreement_Id Varchar2 (25),
      type Varchar2 (25),
       Constraint agreement_Pk Primary Key (agreement_id)
    );

--Create "email" table and add "email_Id" as primary key


    CREATE TABLE email
    ( 
      Id Number (5) ,
      Agreement_Id Varchar2 (25),
      Eamil_Id Varchar2 (25),
       Constraint email_Pk Primary Key (Eamil_Id)
    );

Now added constraints:

1. Foriegn key for "Agreement" table from "user" table:

    Alter Table Agreement
    ADD CONSTRAINT fk_agreement1
    Foreign Key (Id_Two)
    REFERENCES user(id)

2. Foreign key for "email" table from "Agreement" table:


    Alter Table Email
    ADD CONSTRAINT fk_email1
    Foreign Key (Agreement_Id)
    REFERENCES Agreement(Agreement_Id)

3. Foreign key for "email" table from "user" table:

    Alter Table Email
    ADD CONSTRAINT fk_email2
    Foreign Key (id)
    REFERENCES user(id)

Thus, you can add all constraints.

score 0 · Answer 3 · answered Aug 11 '15 at 13:06

0

You have to give a different name to your foreign key (the name seems to be already taken). Change the name and it should work.

CONSTRAINT fk_email_user FOREIGN KEY (id) REFERENCES user(id);

answered Aug 11 '15 at 13:06

kolm3

1

2 foreign key references for a single primary key

3 Answers3