How to list out all Foreign Keys with "WITH NOCHECK" in SQL Server

foreign-keys, sql-server, sql-server-2005, system

Solution

The following will return the name of the foreign keys in the current database that are disabled i.e. WITH NOCHECK

For SQL Server 2005/2008:

select * from sys.foreign_keys where is_disabled=1

There was some discussion in the answer about the difference between disabled & not trusted. What's below explains the differnce Here's some code to clarify the difference between is_disabled & isnotrusted.

-- drop table t1
-- drop table t2
create table t1(i int not null, fk int not null)
create table t2(i int not null)
-- create primary key on t2
alter table t2
add constraint pk_1 primary key (i)
-- create foriegn key on t1
alter table t1
add constraint fk_1 foreign key (fk)
    references t2 (i)
--insert some records
insert t2 values(100)
insert t2 values(200)
insert t2 values(300)
insert t2 values(400)
insert t2 values(500)
insert t1 values(1,100)
insert t1 values(2,100)
insert t1 values(3,500)
insert t1 values(4,500)
----------------------------
-- 1. enabled and trusted
select name,is_disabled,is_not_trusted from sys.foreign_keys
GO

-- 2. disable the constraint
alter table t1 NOCHECK CONSTRAINT fk_1
select name,is_disabled,is_not_trusted from sys.foreign_keys
GO

-- 3. re-enable constraint, data isnt checked, so not trusted.
-- this means the optimizer will still have to check the column
alter table  t1 CHECK CONSTRAINT fk_1 
select name,is_disabled,is_not_trusted from sys.foreign_keys
GO

--4. drop the foreign key constraint & re-add 
-- it making sure its checked
-- constraint is then enabled and trusted
alter table t1  DROP CONSTRAINT fk_1
alter table t1 WITH CHECK 
add constraint fk_1 foreign key (fk)
    references t2 (i)
select name,is_disabled,is_not_trusted from sys.foreign_keys
GO


--5. drop the foreign key constraint & add but dont check
-- constraint is then enabled, but not trusted
alter table t1  DROP CONSTRAINT fk_1
alter table t1 WITH NOCHECK 
add constraint fk_1 foreign key (fk)
    references t2 (i)
select name,is_disabled,is_not_trusted from sys.foreign_keys
GO

`is_disabled` means the constraint is disabled

`isnottrusted` means that SQL Server does not trust that the column has been checked against the foreign key table.

Thus it cannot be assumed that re-enabling the foreign key constraint will be optimized. To ensure the optimizer trusts the column, it's best to drop the foreign key constraint & re-create it with the `WITH CHECK` option (4.)

Problem

Original source