CODEWITHSUNDEEP
mysqlreferenceinsertforeign-keys
user2915539
user2915539

Reputation: 11

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

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?

Upvotes: 1

Views: 1934

Answers (3)

hardmath
hardmath

Reputation: 8823

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.

Upvotes: 0

Cyassin
Cyassin

Reputation: 1490

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.

Upvotes: 1

user1864610
user1864610

Reputation:

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

Upvotes: 0

Related Questions