Trying to INSERT values into tables that contain FOREIGN KEYS that reference each other

Question

So i'm trying to make a database based off a spec sheet (was given an ER diagram and a DB Schema) and said sheet specifies that the first two table contain references to each other, like so:

WAREHOUSE
Column name Data type   Comments
warehouseID VARCHAR(3)  Primary key
street      VARCHAR(20) 
city        VARCHAR(15) 
state       VARCHAR(3)  Examples – VIC, NSW, QLD
postcode    VARCHAR(4)  Examples – 3350, 2001, 3001
managerID   INT         FK–References EMPLOYEE.StaffID

EMPLOYEE
Column name Data type   Comments
staffID     INT A_I     Primary key 
surname     VARCHAR(20) 
firstName   VARCHAR(15) 
dob Date    Short date– Example 06/09/1982
street      VARCHAR(20) 
city        VARCHAR(15) 
state       VARCHAR(3)  Examples – VIC, NSW, QLD, TAS
postcode    VARCHAR(4)  Examples – 3350, 3355, 2001, 3001
salary      Decimal(19,4)   
warehouseID VARCHAR(3)  FK – References WAREHOUSE. warehouseID
supervisedBy INT        FK – References EMPLOYEE.StaffID

My MySQL creation code is as follows *I added the last column to "Warehouse" after table creation because i was unable creat ehm one after the other due to the FK's contained in both:

CREATE DATABASE IF NOT EXISTS WareMart_30114465;
USE WareMart_30114465;
CREATE TABLE IF NOT EXISTS Warehouse
(
warehouseID VARCHAR(3) NOT NULL,
street VARCHAR(20),
city VARCHAR(15), 
statesh VARCHAR(3), 
postcode VARCHAR(4),
PRIMARY KEY (warehouseID)
);
CREATE DATABASE IF NOT EXISTS WareMart_30114465;
USE WareMart_30114465;
CREATE TABLE Employee
(
staffID INT NOT NULL AUTO_INCREMENT,
surname VARCHAR(20),
firstName VARCHAR(15),
dob date,
street VARCHAR(20),
city VARCHAR(15), 
statesh VARCHAR(3), 
postcode VARCHAR(4), 
salary DECIMAL(19,4),
warehouseID VARCHAR(3),
PRIMARY KEY (staffID),
FOREIGN KEY (warehouseID) REFERENCES Warehouse(warehouseID)
);
CREATE DATABASE IF NOT EXISTS WareMart_30114465;
USE WareMart_30114465;
ALTER TABLE Warehouse ADD managerID INT NOT NULL;
ALTER TABLE Warehouse ADD CONSTRAINT managerID 
FOREIGN KEY (managerID) REFERENCES Employee(staffID)
ON UPDATE CASCADE
ON DELETE CASCADE;

Of course with the FK's being set up the way they are (and have to be unless atm i can do it somehow else), i cant seems to inert anything with out getting an error. Current Insert code as follows:

USE WareMart_30114465;
INSERT INTO Warehouse (warehouseID, street, city, statesh, postcode)
VALUES ('W01', 'SturtSt', 'Ballarat', 'VIC', '3350' );
INSERT INTO Employee (staffID, surname, firstName, dob, street, city, statesh,       postcode, salary, warehouseID)
VALUES (1, 'Smith', 'John', 01/02/03, 'Example St', 'GenericPlace', 'ABC', '1234',

Personally am at a loss as to why i have to set up a circle/Chicken-Egg reference, as i thought that was against conventions.

Any thoughts?

Cyassin · Answer 1 · 2014-05-26T05:46:47.380

I do not know if it is against your specs and if they are fixed. But personally I would add another table with different employee positions. e.g 1 = Manager, 2= Standard Employee, etc...

Then add a field in the Employee table called position or something relevant.

Then the relationship would be singular between the warehouse and employee table and then when searching in the query you would select employee where warehouseId matches and position field is manager.

Sorry for no working example. An example is like:

Select secUser.* from secUser JOIN secUserRole ON secUser.pkUser = secUserRole.fkUser WHERE fkRole = 0

In which the above gets all user details where the UserRole table's Role value = 0

Warehouse

WarehouseID

etc.

Employee

EmployeeID

PositionID

etc.

Position

PositionID

Name

etc.

I'll check if i can alter the structure as i had a similar idea to you, creating "proxy tables" that both forementioned tables could read from, but i wont implement anything like till i know i can :) — user2915539, May 26 '14 at 06:49

score 0 · Answer 2 · answered May 26 '14 at 05:25

0

The problem will only occur when inserting a warehouse, and since inserting a warehouse is likely to be a rare occurrence you can turn off the foreign key checks while you do it with:

SET foreign_key_checks = 0;

and enable it when you're done with

SET foreign_key_checks = 1;

The MySQL reference is here

answered May 26 '14 at 05:25

Thanks this is working for me so far, using this to insert into warehouse: `USE WareMart_30114465; SET foreign_key_checks = 0; INSERT INTO Warehouse (warehouseID, street, city, statesh, postcode) VALUES ('W01', 'SturtSt', 'Ballarat', 'VIC', '3350' );` However im getting an error with the following to insert into employee: `INSERT INTO Employee (staffID, surname, firstName, dob, street, city, statesh, postcode, salary, warehouseID, supervisedBy) VALUES (1, 'Smith', 'John', 01/02/03, 'Example St', 'GenericPlace', 'ABC', '1234', 654 , , );` – user2915539 May 26 '14 at 06:41

score 0 · Answer 3 · edited May 23 '17 at 10:32

The conventional way to solve the "chicken and egg" dilemma on inserting records into tables with mutual FOREIGN KEY dependencies is to allow NULL for a foreign key constrained column in one of the two tables.

For example, instead of NOT NULL on the managerID of the WAREHOUSE table, you would define the table to allow Nulls in this field. Then the WAREHOUSE records could be INSERTed before the EMPLOYEE records that correspond to the managerID you want to assign, temporarily putting a null value there. Subsequently the WAREHOUSE record can be updated with a correct managerID.

This is generally considered better than turning off the checking of foreign keys entirely because that is a "global" setting, and you don't want to assume your task is the only one running.

I can see why disabling FK checking may be considered the less used option, but given the simplicity of the DB im sposed to be creating ( a create.sql, a populate.sql and a query.sql file are all thats needed) i think i may just use it any way. Thanks though :) — user2915539, May 26 '14 at 06:47

Trying to INSERT values into tables that contain FOREIGN KEYS that reference each other

3 Answers3