Database contains foreign key that doesn exist even though there is a foreign key constraint?-CodePudding

I am working with a database where there is a foreign key ID that doesn't exist even though there is a foreign key constraint.

There is a table called "Workplace" with a foreign key column called "AddressID" pointing to another table called "Address"

The foreign key is as follows:

ALTER TABLE [dbo].[Workplace]  WITH NOCHECK ADD  CONSTRAINT [FK_Workplace_Address] FOREIGN KEY([AddressID])
REFERENCES [dbo].[Address] ([ID])
GO

ALTER TABLE [dbo].[Workplace] CHECK CONSTRAINT [FK_Workplace_Address]
GO

For one particular row in the Workplace table, the AddressID has a value of "1". When I run select * from Address where ID = 1 there is no result.

Then I ran update Workplace set AddressID = 3 where Workplace.ID = 20 the value changed to 3 and I have verified that an Address with ID 3 exists.

Then I ran update Workplace set AddressID = 1 where Workplace.ID = 20 again and I get the error

The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_Workplace_Address". The conflict occurred in database db_name, table "dbo.Address", column 'ID'.

I don't understand how the value 1 could have been put there in the first place. The Address with ID=1 couldn't have been deleted after the constraint was put in place. The constraint creation would also fail if the record was deleted in the first place. Does anyone know how this could be possible?

This is a database on Windows Server 2016, SQL Server 12.0.4237.0

CodePudding user response：

You create your FOREIGN KEY with NOCHECK, as a result the values that already exist in the table are not checked. This can be replicated with the following:

CREATE TABLE dbo.Address (ID int NOT NULL CONSTRAINT PK_Address PRIMARY KEY);
GO
CREATE TABLE dbo.Workplace (ID int NOT NULL CONSTRAINT PK_Workplace PRIMARY KEY,
                            AddressID int)
GO

INSERT INTO dbo.Workplace
VALUES(1,3); --Works
GO

ALTER TABLE dbo.WorkPlace  WITH NOCHECK ADD CONSTRAINT FK_Workplace_Address FOREIGN KEY (AddressID) REFERENCES dbo.Address(ID);
GO

ALTER TABLE dbo.Workplace CHECK CONSTRAINT FK_Workplace_Address;
GO

INSERT INTO dbo.Workplace
VALUES(2,3); --Fails
GO

UPDATE dbo.Workplace
SET AddressID = 2
WHERE ID = 1; --Fails
GO

DROP TABLE dbo.Workplace;
DROP TABLE dbo.Address;

As you can see, only rows that are INSERTed or UPDATEd after the constraint was created (with NOCHECK) are validated; the first row INSERTed is left as it was, with a reference to a row that does not exist.

Instead, create the key without NOCHECK defined, or with CHECK, and the statement will fail when you try to create it:

CREATE TABLE dbo.Address (ID int NOT NULL CONSTRAINT PK_Address PRIMARY KEY);
GO
CREATE TABLE dbo.Workplace (ID int NOT NULL CONSTRAINT PK_Workplace PRIMARY KEY,
                            AddressID int)
GO

INSERT INTO dbo.Workplace
VALUES(1,3); --Works
GO

ALTER TABLE dbo.WorkPlace  WITH CHECK ADD CONSTRAINT FK_Workplace_Address FOREIGN KEY (AddressID) REFERENCES dbo.Address(ID); --Fails
GO
DROP TABLE dbo.Workplace;
DROP TABLE dbo.Address;

This generates the error below:

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Workplace_Address". The conflict occurred in database "Sandbox", table "dbo.Address", column 'ID'.

Alternatively, create the key when you create the tables; though it's likely too late for that now.

CREATE TABLE dbo.Address (ID int NOT NULL CONSTRAINT PK_Address PRIMARY KEY);
GO

CREATE TABLE dbo.Workplace (ID int NOT NULL CONSTRAINT PK_Workplace PRIMARY KEY,
                            AddressID int CONSTRAINT FK_Workplace_Address FOREIGN KEY REFERENCES dbo.Address(ID));
GO

INSERT INTO dbo.Workplace
VALUES(1,3); --Fails

GO

DROP TABLE dbo.Workplace;
DROP TABLE dbo.Address;