CODEWITHSUNDEEP
mysqlsql
WP0987
WP0987

Reputation: 179

What is the correct way of have foreign keys in tables that reference each other

The tables will build, but every time I try to insert values into the table I get a 1452 error of foreign key constraints fails. I wonder if the problem has to do with EMPLOYEE table has a foreign key for STORE_CODE in the STORE table, and STORE table has a foreign key for EMP_CODE in EMPLOYEE table. Is the circular reference the problem here?

ALTER TABLE EMPLOYEE DROP FOREIGN KEY STORE_CD;
ALTER TABLE STORE DROP FOREIGN KEY REGION_CD;
ALTER TABLE STORE DROP FOREIGN KEY EMPLOYEE_CD;

DROP TABLE IF EXISTS EMPLOYEE, REGION, STORE;

CREATE TABLE EMPLOYEE (
    EMP_CODE int NOT NULL PRIMARY KEY,
    EMP_TITLE varchar(4),
    EMP_LNAME varchar(15),
    EMP_FNAME varchar(15),
    EMP_INITIAL varchar(1),
    EMP_DOB datetime,
    STORE_CODE int NOT NULL
) Engine=InnoDB;

-- Table Region
CREATE TABLE REGION (
    REGION_CODE int NOT NULL PRIMARY KEY,
    REGION_DESCRIPT varchar(20)
) Engine=InnoDB;

-- Table Store
CREATE TABLE STORE (
    STORE_CODE int NOT NULL PRIMARY KEY,
    STORE_NAME varchar(20)  NOT NULL,
    STORE_YTD_SALES numeric  NOT NULL,
    REGION_CODE int  NOT NULL,
    EMP_CODE int  NOT NULL
) Engine=InnoDB;



ALTER TABLE EMPLOYEE ADD CONSTRAINT STORE_CD 
      FOREIGN KEY STORE_CD(STORE_CODE) REFERENCES STORE(STORE_CODE);

ALTER TABLE STORE ADD CONSTRAINT REGION_CD 
      FOREIGN KEY REGION_CD(REGION_CODE) REFERENCES REGION(REGION_CODE);

ALTER TABLE STORE ADD CONSTRAINT EMPLOYEE_CD 
      FOREIGN KEY EMPLOYEE_CD(EMP_CODE) REFERENCES EMPLOYEE(EMP_CODE);

Upvotes: 1

Views: 1765

Answers (4)

Yazsid
Yazsid

Reputation: 165

In my experience with relational databases, I think you should create an intermediate table to conect "store" with "employee" (lets name it (store_has_employee) with the atributes(idstore(fk), idemployee(fk) and isManager(boolean)).

Then you should insert the "regions" first, so you can insert a "store", then when you have registered "employees", all you have to do is conect them in "store_has_employee", and if you want to say that is the manager, just insert isManager=true.

This is the most eficient way to do it and to get faster queries.

Hope it helps.

Upvotes: 1

WP0987
WP0987

Reputation: 179

Due to my lack of understanding SQL, the simplest solution for me has been to remove the foreign key from the employee table so that I don't have a circular reference. Then populate the employee table first the other tables afterwards.

Upvotes: 0

Rakesh
Rakesh

Reputation: 1035

Which one you Want to insert first? If EMPLOYEE then Make STORE_CD (nullable=true) in EMPLOYEE After that Insert STORE item with EMPLOYEE id and Update EMPLOYEE with store code.You can use Transaction for this whole process.

Upvotes: 0

Barmar
Barmar

Reputation: 780818

It's not possible to have mutual foreign keys unless you allow at least one of the columns to be NULL. Otherwise you can never have a consistent set of tables: If you add the store first, it will refer to a nonexistent employee; if you add the employee first, it will refer to a nonexistent store.

So you need to allow the referencing column to be NULL. Then you can add a row to the first table with NULL in the referencing column, add a row to the second table, then fill in the referencing column in the first table with the ID from the second table.

Upvotes: 5

Related Questions