undocumented feature/bug WITH CHECK CHECK

undocumented feature/bug WITH CHECK CHECK

Post by joe_ce.. » Wed, 08 Mar 2000 04:00:00



In real SQL-92, this is done by declaring the constraints to be [NOT]
DEFFERABLE when you create the table.  You then set the constraints off
for your transaction. The constraitns will switch back on when you
issue an explicit command or at the end of your session.  The rule is
that you cannot leave a session with the database in a state that
violates any constraint.

--CELKO--

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

undocumented feature/bug WITH CHECK CHECK

Post by Roland Zumkelle » Thu, 09 Mar 2000 04:00:00


I guess I found an undocumented feature/bug in SQL Server 7.0, and just
want to know what you guys think about it or if it is really new.

Let's say you have a foreign key constraint called myRef on a table
myTab. You want to disable it for a while, insert some rows, and then
enable it again to see if the inserts were o.k..
You would use:
  ALTER TABLE myTab NOCHECK CONSTRAINT myRef
  <do some inserts>
Now when you use "ALTER TABLE myTab CHECK CONSTRAINT myRef" only future
inserts will be checked, but not the rows, which are already in the
table. It seems that this can be achieved with the following statement:
  ALTER TABLE myTab WITH CHECK CHECK CONSTRAINT myRef
This doesn't comply with the syntax in BOL, but it seems to do its job.
Strange enough, also "ALTER TABLE myTab WITH CHECK NOCHECK CONSTRAINT
myRef" is accepted, but I can't imagine what it does.

Since the things I achieved using this code don't seem to be that
uncommon to me, I wonder how you do backwards-checking normally. Or was
I just to dumb to read the documentation properly?

Roland

Sorry if this posting appears twice, but I had problems with the date
setting.

 
 
 

1. beckwards check disabled constraints (undocumented?)

I guess I found an undocumented feature/bug in SQL Server 7.0, and just
want to know what you guys think about it or if it is really new.

Let's say you have a foreign key constraint called myRef on a table
myTab. You want to disable it for a while, insert some rows, and then
enable it again to see if the inserts were o.k..
You would use:
  ALTER TABLE myTab NOCHECK CONSTRAINT myRef
  <do some inserts>
Now when you use "ALTER TABLE myTab CHECK CONSTRAINT myRef" only future
inserts will be checked, but not the rows, which are already in the
table. It seems that this can be achieved with the following statement:
  ALTER TABLE myTab WITH CHECK CHECK CONSTRAINT myRef
This doesn't comply with the syntax in BOL, but it seems to do its job.
Strange enough, also "ALTER TABLE myTab WITH CHECK NOCHECK CONSTRAINT
myRef" is accepted, but I can't imagine what it does.

Since the things I achieved using this code don't seem to be that
uncommon to me, I wonder how you do backwards-checking normally. Or was
I just to dumb to read the documentation properly?

Roland

Sorry if this posting appears twice, but I had problems with the date
setting.

2. Local cubes from virtual cubes

3. Syntax Check feature buggy?

4. Changing field properties in replication

5. a new exciting new feature added to Odat, a free dba tool, check it out

6. How to organize table that defines virtual areas in SQL ?

7. Ingres/Platfrom Performance and Feature Check

8. NULLS in CHAR field types?

9. Check constraint w UDF on Update doesnt check

10. Column check constraint vs table check constraint

11. Consistancy Check or index check crashes my machine

12. DELETE does not check CHECK constraint

13. Change table check constraint to column check constraint?