Deferred constraints sql server 2005

Is there any way to defer constriants in sql server 2005? I have found some sites that say use the keyword deferred but that always give me an "Incorrect syntax near 'deferred' "error.

Hi Jeff,

Can you expand on the meaning of "deferred constraint", please?

If what you are trying, is to disable a constraint to do some bulk operations and then enable it back, then see "alter table" in BOL.

with check / nocheck --> is to check or nocheck the data when you enable the constraint

check / nocheck --> to enable / disable the constraint

Example:

use tempdb

go

create table dbo.t1(

c1 int constraint ck_c1 check (c1 > 0)

)

go

insert into dbo.t1 values(-1)

go

alter table dbo.t1

nocheck constraint ck_c1

go

select objectproperty(object_id('ck_c1'), 'CnstIsDisabled')

go

insert into dbo.t1 values(-1)

go

select * from dbo.t1

go

alter table dbo.t1

with nocheck check constraint ck_c1

go

select objectproperty(object_id('ck_c1'), 'CnstIsNotTrusted')

go

-- will give error because there is a value breaking the constraint

alter table dbo.t1

with check check constraint ck_c1

go

delete dbo.t1 where c1 = -1

go

alter table dbo.t1

with check check constraint ck_c1

go

select objectproperty(object_id('ck_c1'), 'CnstIsNotTrusted')

go

drop table dbo.t1

go

Be careful when enabling the constraint back, remember to use "with check" to make is trusted. The query optimizer uses constraints when choosing the execution plan, but they need to be trusted.

AMB