The INSERT statement conflicted with the FOREIGN KEY constraint"

Question

I'm trying to insert data into my SQL Server 2014 build and I keep getting this error. I tried searching all over the internet and at my whits end, I sort of get what people were saying but still having difficulty understanding what's wrong with my particular code.

All tables are empty without data. I cannot add data once the foreign keys are set. I read that both tables must be populated for this to work but how does that happen when I can't add anything? If I add data before the foreign key, then I'm unable to add foreign keys. Please help!

When trying to insert data into Gym row using the INSERT INTO I get this error:

The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Gym__staffNo". The conflict occurred in database "FitnessApp", table "dbo.Staff", column 'staffNo'.

This also happens when trying to add data into the Equipment or Staff tables as well.

See code here:

Schema:

CREATE TABLE Gym
(
gymNo int NOT NULL IDENTITY(1,1),
staffNo int NOT NULL,
streetAddress varchar(100) NOT NULL,
streetAddress2 varchar(100) NULL,
city varchar(50) NOT NULL,
state char(2) NOT NULL,
zip char(5) NOT NULL,
phone char(10) NOT NULL
) ON [fitnessPlusGroup1];

CREATE TABLE Staff
(
staffNo int NOT NULL IDENTITY(1,1),
gymNo int NOT NULL,
position varchar(50) NOT NULL,
firstName varchar(50) NOT NULL,
lastName varchar(50) NOT NULL,
streetAddress varchar(100) NOT NULL,
streetAddress2 varchar(100) NULL,
city varchar(50) NOT NULL,
state char(2) NOT NULL,
zip char(5) NOT NULL,
phone char(10) NOT NULL,
hireDate date NOT NULL
) ON [fitnessPlusGroup1];

CREATE TABLE Member
(
memberNo int NOT NULL IDENTITY(1,1),
gymNo int NOT NULL,
firstName varchar(30) NOT NULL,
lastName varchar(30) NOT NULL,
streetAddress varchar(100) NOT NULL,
streetAddress2 varchar(100) NULL,
city varchar(50) NOT NULL,
state char(2) NOT NULL,
zip char(5) NOT NULL,
phone char(10) NOT NULL,
memberSince date NOT NULL,
scheduleID int NULL
) ON [fitnessPlusGroup1];

CREATE TABLE Schedule
(
scheduleID int NOT NULL IDENTITY(1,1),
staffNo int NOT NULL,
trainDate date NOT NULL,
trainTime time(0) NOT NULL
) ON [fitnessPlusGroup1];

CREATE TABLE Equipment
(
equipNo int NOT NULL IDENTITY(1,1),
gymNo int NOT NULL,
staffNo int NOT NULL,
name varchar(50) NOT NULL,
quantity int NOT NULL
) ON [fitnessPlusGroup1];

Foreign key relationships setup:

ALTER TABLE Gym
ADD FOREIGN KEY (staffNo) REFERENCES Staff(staffNo);

ALTER TABLE Staff
ADD FOREIGN KEY (gymNo) REFERENCES Gym(gymNo); 

ALTER TABLE Member
ADD FOREIGN KEY (gymNo) REFERENCES Gym(gymNo);

ALTER TABLE Member
ADD FOREIGN KEY (scheduleID) REFERENCES Schedule(scheduleID);

ALTER TABLE Schedule
ADD FOREIGN KEY (staffNo) REFERENCES Staff(staffNo);

ALTER TABLE Equipment
ADD FOREIGN KEY (gymNo) REFERENCES Gym(gymNo);

ALTER TABLE Equipment
ADD FOREIGN KEY (staffNo) REFERENCES Staff(staffNo);

The insert commands that give errors:

INSERT INTO Gym(staffNo, streetAddress, streetAddress2, city, state, zip, phone) 
VALUES (1, '7300 W Greens Rd', NULL, 'Houston', 'TX', '77064', '2818946151');

Any help would be much appreciated sorry if this seems like a bit much and I hope I provided all the info I could..

Answer 1

I think the problem is in your table design. Take these two FKEY, as an exmpale:

ALTER TABLE Gym
ADD FOREIGN KEY (staffNo) REFERENCES Staff(staffNo);

ALTER TABLE Staff
ADD FOREIGN KEY (gymNo) REFERENCES Gym(gymNo); 

You cannot add a record to Gym until you've added the supporting staffNo to Staff. But you cannot add a record to Staff until you've added the supporting gymNo to Gym. These keys prevent you populating either table, as both require records to be present in the other.

Why is this? Because an FKey is like a promise. It guarantees that the value in column x can always be found in table y. In order to fulfill this promise table y must be populated first. But when you have a circular reference, back to the original table, this can never be achieved.

Here is one possible solution. You could remove the staffNo from Gym and GymNo from Staff. Then add a new table StaffGym. This table would have two fields staffNo and gymNo. It would be populated after Staff and Gym, providing a bridge between the two. This is called a cross reference table, or sometimes xref for short.

Answer 2

You say:

All tables are empty without data.

So there is no staffNo with id = 1 in your Staff. You've got a foreign key constraint so you can only reference items that exist. So you first need to populate the lookup data in Staff before you can link to it with an ID.

Also, remove one of the constraint between Staff and Gym, you have this:

ALTER TABLE Gym
ADD FOREIGN KEY (staffNo)
REFERENCES Staff(staffNo);

ALTER TABLE Staff
ADD FOREIGN KEY (gymNo)
REFERENCES Gym(gymNo);

When you only need one of these. I'd say remove it from Gym, as a Gym could have many staff I'd assume, and also remove the StaffNo column from Gym. As this is preventing you from creating one without the other. Once you've removed that link, you should insert data in the correct order.

The order of the inserts is key. If you remove the column I suggested above, you should insert in the following order based on your constraints:

1. Gym
2. Staff (requires gym record)
3. Schedule (requires staff record)
4. Member (requires schedule record)
5. Equipment (requires gym and staff record)