SQL Server Foreign Key still not trusted after CHECK CHECK

Question

Today I found some foreign key constraints in one of our SQL Server datbases which were not trusted using the following statement:

SELECT name, is_not_trusted
FROM   sys.foreign_keys
WHERE is_not_trusted = 1

I tried to fix the problem quickly using the following syntax and thought that the second check will ensure the FK is trusted again.

ALTER TABLE dbo.<TableName>
  WITH CHECK CHECK CONSTRAINT <FK_NAME>

The statement completed successfully but the FK is still reported as "Not trusted". I did not find any reason therefore? Does anyone have an idea?

I already have a workaround. Dropping and Recreating the FK fixes the problem but I still wonder why the ALTER command did have no effect

Please share table script and foreign key definition. Check also if FK is enabled. Do you have replication enabled? — Lukasz Szozda, Mar 09 '16 at 13:10
`SELECT name, is_not_for_replication FROM sys.foreign_keys WHERE is_not_trusted = 1` — Lukasz Szozda, Mar 09 '16 at 13:18
see this question:http://stackoverflow.com/questions/16312866/how-to-make-foreign-key-constraints-trusted — TheGameiswar, Mar 09 '16 at 13:34

Lukasz Szozda · Accepted Answer · 2016-03-09T13:34:27.617

My guess is that you have constraint created with NOT FOR REPLICATION:

CREATE TABLE val(id INT PRIMARY KEY, name VARCHAR(100));

CREATE TABLE tab(id INT IDENTITY(1,1) PRIMARY KEY, 
                 val_id INT);
GO

ALTER TABLE tab WITH NOCHECK 
ADD CONSTRAINT fk_val FOREIGN KEY (val_id) REFERENCES val(Id) NOT FOR REPLICATION;

ALTER TABLE tab NOCHECK CONSTRAINT fk_val;
GO


-- Insert Data
INSERT INTO tab(val_id)
VALUES (10),(20);
GO

INSERT INTO val(id)
VALUES(10),(20);

-- check data
ALTER TABLE tab WITH CHECK CHECK CONSTRAINT fk_val;

-- check is_trusted
SELECT name, is_not_trusted, is_not_for_replication
FROM   sys.foreign_keys
WHERE is_not_trusted = 1;

LiveDemo

Output:

╔═══╦════════╦════════════════╦════════════════════════╗
║   ║  name  ║ is_not_trusted ║ is_not_for_replication ║
╠═══╬════════╬════════════════╬════════════════════════╣
║ 1 ║ fk_val ║ True           ║ True                   ║
╚═══╩════════╩════════════════╩════════════════════════╝

That's it. Thank you very much! – Tobi DM Mar 09 '16 at 13:33 — Tobi DM, Mar 09 '16 at 13:33

SQL Server Foreign Key still not trusted after CHECK CHECK

1 Answers1