defer constraint checking

Question

defer constraint checking

Martha Sharkey

SSC Enthusiast

Points: 100
More actions
March 17, 2005 at 10:42 pm

#85412

In running an update transaction on a table (not alter table, but update data in the table), I'd like to be able to duplicate oracle's set constraint all deferred/commit structure so that checking isn't done until the commit is executed. Is there a way to do this?

Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

No, SQL Server does not know the concept of constraint deferability.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Martha Sharkey SSC Enthusiast Points: 100 More actions · Answer 2

I'm unpleasantly surprised, since this appears to be a basic concept in Informix and Oracle.

Can it be simulated, perhaps by turning off particular named constraints, then turning them back on before the commit? I've seen an article where turning it back on then issuing another select will cause it to recheck the whole table, can anyone confirm this?

Thanks.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Yes, that's a way to achieve somethings similar. Have a look at BOL for ALTER TABLE. Section G. deals with how to disable and reenable a constraint.

Btw, not everything what other RDBMS can do, is better. Personally I think this "lack" of functionality in SQL Server, is a great help in keeping garbage out of the db.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]