SQL Server 2008 R2 sys.foreign_keys, is_not_trusted column

that setting is effected by NoCheck setting while creating constraint. below is the sample.

use tempdb
go
drop table foo, bar;

create table foo(i int primary key)
go
create table bar(i int primary key)
go
insert into foo values (2)
go
insert into bar values (1)
go
-- Below would give error
ALTER TABLE foo 
ADD CONSTRAINT FK_foo_bar FOREIGN KEY(i)
REFERENCES bar (i)
go
-- Below would succeed 
ALTER TABLE foo WITH NOCHECK 
ADD CONSTRAINT FK_foo_bar FOREIGN KEY(i)
REFERENCES bar (i)
go

-- We created the FK constraint without checking existing records.

select name, is_not_trusted from sys.foreign_keys

---

Balmukund Lakhani

Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker

--------------------------------------------------------------------------------

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------------------------------------------------------------------

My Blog | Team Blog | @Twitter | Facebook

Author: SQL Server 2012 AlwaysOn - Paperback, Kindle