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

Question

Hi

Does anyone know a query for listing out all foreign keys in a database with "WITH NOCHECK" description applied to it? (removing them will boost performance and stability).

Answer 1

WITH NOCHECK should only ever be applied to FK's temporarily, or they become useless to the optimiser as your linked article points out. From BOL:

The query optimizer does not consider constraints that are defined WITH NOCHECK. Such constraints are ignored until they are re-enabled by using ALTER TABLE table CHECK CONSTRAINT ALL.

This will identify all your Foreign Keys: (working on the WITH NOCHECK bit...)

SELECT C.TABLE_CATALOG [PKTABLE_QUALIFIER], 
       C.TABLE_SCHEMA [PKTABLE_OWNER], 
       C.TABLE_NAME [PKTABLE_NAME], 
       KCU.COLUMN_NAME [PKCOLUMN_NAME], 
       C2.TABLE_CATALOG [FKTABLE_QUALIFIER], 
       C2.TABLE_SCHEMA [FKTABLE_OWNER], 
       C2.TABLE_NAME [FKTABLE_NAME], 
       KCU2.COLUMN_NAME [FKCOLUMN_NAME], 
       RC.UPDATE_RULE, 
       RC.DELETE_RULE, 
       C.CONSTRAINT_NAME [FK_NAME], 
       C2.CONSTRAINT_NAME [PK_NAME], 
       CAST(7 AS SMALLINT) [DEFERRABILITY] 
FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS C 
       INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU 
         ON C.CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA 
            AND C.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME 
       INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC 
         ON C.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA 
            AND C.CONSTRAINT_NAME = RC.CONSTRAINT_NAME 
       INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C2 
         ON RC.UNIQUE_CONSTRAINT_SCHEMA = C2.CONSTRAINT_SCHEMA 
            AND RC.UNIQUE_CONSTRAINT_NAME = C2.CONSTRAINT_NAME 
       INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2 
         ON C2.CONSTRAINT_SCHEMA = KCU2.CONSTRAINT_SCHEMA 
            AND C2.CONSTRAINT_NAME = KCU2.CONSTRAINT_NAME 
            AND KCU.ORDINAL_POSITION = KCU2.ORDINAL_POSITION 
WHERE  C.CONSTRAINT_TYPE = 'FOREIGN KEY'

Ref.

As an aside, in both SQL Server 2000 and 2005, you can check if any data violates a constraint using:

DBCC CHECKCONSTRAINTS (table_name)

Answer 2

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

Answer 3

SELECT * FROM sys.foreign_keys AS f Where Is_Not_Trusted = 1

Answer 4

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, its best to drop the foreign key constraint & re-create it with the WITH CHECK option (4.)