alter table <table_name> nocheck constraint all QUESTION.

In a stored procedure we use te following construction.
alter table <table_name> nocheck constraint all
<working code>
alter table <table_name> check constraint all
(For more than one table).
(Before and after the transaction the db is consistent,
just not during the transaction).
At the time of switching the constraint on all
constraints should be adhered to, but this is not
checked.
Can the check be done when it is switched back on ?
A check that does not check the complete table, but
just the altered rows ?
thanks for your attention,
ben brugman
EXPLANATION :
Why do we use this switching of of constraints.
In a similar case in Oracle we use defered constraints.
A very oversimplified explenations :
We have a master table and a detail table. The key of
the master table has to be altered so the key FK of the
detail table has to be altered as wel. If the constraint is
left on the first update gives a constraint violation.
The total transaction does updates in over 40 tables with
between 50 and 60 constraints between the those tables.
Ben,
When you re-enable a constraint, you can do that either with check or with nocheck. Looks a bit
funny, though:
alter table <table_name> WITH CHECK check constraint all
I have a feeling that SLQ Server will not only go though the newly added/modified rows, though. A
quick test should tell you that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ben brugman" <ben@.niethier.nl> wrote in message news:e3RkyILlEHA.644@.tk2msftngp13.phx.gbl...
> In a stored procedure we use te following construction.
> alter table <table_name> nocheck constraint all
> <working code>
> alter table <table_name> check constraint all
> (For more than one table).
> (Before and after the transaction the db is consistent,
> just not during the transaction).
> At the time of switching the constraint on all
> constraints should be adhered to, but this is not
> checked.
> Can the check be done when it is switched back on ?
> A check that does not check the complete table, but
> just the altered rows ?
> thanks for your attention,
> ben brugman
> EXPLANATION :
> Why do we use this switching of of constraints.
> In a similar case in Oracle we use defered constraints.
> A very oversimplified explenations :
> We have a master table and a detail table. The key of
> the master table has to be altered so the key FK of the
> detail table has to be altered as wel. If the constraint is
> left on the first update gives a constraint violation.
> The total transaction does updates in over 40 tables with
> between 50 and 60 constraints between the those tables.
>
>
|||Thanks Tibor,
You jolted my memory.
I did consider the with check option before but because
of the longer check time did not use it. As said the consistency
before and after the transaction 'should' be correct.
At the moment the complete Stored procedure takes about
6 seconds (was 2 secs) and does only a few (less than 10) updates in that
time. Most time probably goes into getting the resources, doing
no updates on them (because no rows are selected) and
giving the resources back. (And doing this for over 40 tables).
I am looking into cascaded update for this. Probably far more efficient,
but I do not dare to take the plunge yet.
Should do some test with that though.
Thanks for your quick response,
ben brugman
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uknFQlLlEHA.2380@.TK2MSFTNGP14.phx.gbl...
> Ben,
> When you re-enable a constraint, you can do that either with check or with
nocheck. Looks a bit
> funny, though:
> alter table <table_name> WITH CHECK check constraint all
> I have a feeling that SLQ Server will not only go though the newly
added/modified rows, though. A
> quick test should tell you that.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "ben brugman" <ben@.niethier.nl> wrote in message
news:e3RkyILlEHA.644@.tk2msftngp13.phx.gbl...
>