Board index MS SQL Server

checking the violation of constraint with existing data (after enabling of the constraint)

checking the violation of constraint with existing data (after enabling of the constraint)

Post by Parvinder Singh Aro » Tue, 21 May 2002 21:38:09



Hi,

temp1.i refers temp.i.
I disable this foriegn key constraint using

alter table tt1 nocheck constraint all

alter table tt1 check constraint all

and add some data in temp1 (which is not in temp.i)
after adding some data i enable the constraint
but it doesn't throw a error for the existing data, which violates the
foreign key constraint

Is it possible that after i enable the constraint it should check
whether the existing data violates the constraint or not ?

Hope i am clear with my problem

TIA

~Parvinder

 
 
 
Top

checking the violation of constraint with existing data (after enabling of the constraint)

Post by Itzik Ben-Ga » Tue, 21 May 2002 21:43:14


When you enable a disabled constraint, there's no way to ask SQL Server to
check existing data AFAIK. You have two options:
1. Drop and recreate the constraint.
2. Run a query that verifies whether the existing data is valid.

--
BG

SQL Server MVP
Hi-Tech College, Israel
http://sql.hi-tech.co.il
http://www.tsqlsolutions.com



Quote:> Hi,

> temp1.i refers temp.i.
> I disable this foriegn key constraint using

> alter table tt1 nocheck constraint all

> alter table tt1 check constraint all

> and add some data in temp1 (which is not in temp.i)
> after adding some data i enable the constraint
> but it doesn't throw a error for the existing data, which violates the
> foreign key constraint

> Is it possible that after i enable the constraint it should check
> whether the existing data violates the constraint or not ?

> Hope i am clear with my problem

> TIA

> ~Parvinder


 
 
 
Top

checking the violation of constraint with existing data (after enabling of the constraint)

Post by Vikrant V Dalwale [M » Sun, 26 May 2002 08:04:23


Hello ,

If you are using SQL 7.0 ,  then there is a bug in sql 7.0 ,

BUG #: 54921 (SQLBUG_70)

SYMPTOMS
========

If the following sequence of events occurs the system fails to check the
constraint for new data modifications:

1. An ALTER TABLE statement is issued WITH NOCHECK constraint on a foreign
key.

2. A row is deleted.

3. The table is altered back with check constraint on the foreign key.

WORKAROUND
==========

To work around this problem, use either of the following:

 - Stop and restart SQL Server after re-enabling the check constraint.

   -or-

 - Execute DBCC FREEPROCCACHE after re-enabling the check constraint.

SQL 7.0 SP2 Fixes this problem.

Thanks,

Vikrant Dalwale

Microsoft SQL Server Support Professional

This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.? 2002 Microsoft Corporation. All rights
reserved.

Additional support can be obtained at http://support.microsoft.com

Are you secure?  For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

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


>Newsgroups: microsoft.public.sqlserver.programming
>Subject: checking the violation of constraint with existing data (after

enabling of the constraint)
>Date: 20 May 2002 05:38:09 -0700
>Organization: http://groups.google.com/
>Lines: 22

>NNTP-Posting-Host: 203.124.129.101
>Content-Type: text/plain; charset=ISO-8859-1
>Content-Transfer-Encoding: 8bit
>X-Trace: posting.google.com 1021898290 12625 127.0.0.1 (20 May 2002
12:38:10 GMT)

>NNTP-Posting-Date: 20 May 2002 12:38:10 GMT
>Path:

cpmsftngxa08!tkmsftngp01!newsfeed00.sul.t-online.de!t-online.de!news-spur1.m
axwell.syr.edu!news.maxwell.syr.edu!out.nntp.be!propagator2-SanJose!propagat
or-SanJose!in.nntp.be!easynews!sn-xit-02!supernews.com!postnews1.google.com!
not-for-mail
Quote:>Xref: cpmsftngxa08 microsoft.public.sqlserver.programming:259420
>X-Tomcat-NG: microsoft.public.sqlserver.programming

>Hi,

>temp1.i refers temp.i.
>I disable this foriegn key constraint using

>alter table tt1 nocheck constraint all

>alter table tt1 check constraint all

>and add some data in temp1 (which is not in temp.i)
>after adding some data i enable the constraint
>but it doesn't throw a error for the existing data, which violates the
>foreign key constraint

>Is it possible that after i enable the constraint it should check
>whether the existing data violates the constraint or not ?

>Hope i am clear with my problem

>TIA

>~Parvinder

 
 
 
Top

1. Column check constraint vs table check constraint

If I create a column with a CHECK constraint (either via CREATE
TABLE or ALTER TABLE ADD newcol), the system tables record the
fact that the constraint is a column-level constraint.  For
example
     create table x (pk int identity primary key, x int not null
constraint CK_x_x CHECK(x between 1 and 9))
     sp_helpconstraint 'x'
The constraint is described as "CHECK on column x" in the 1st
column of the 2nd result set.

If the check expression changes -- e.g. if in the new world 10 is
valid -- the obvious (?only?) thing to do is to drop the
constraint and add it back again with the new expression:
     alter table x drop constraint CK_x_x
     alter table x add constraint CK_x_x CHECK(x between 1 and
10)
But now sp_helpconstraint shows the constraint as "CHECK Table
Level".

The syntax diagram for ALTER TABLE indicates that, unless we are
adding a new column, we can only add what it calls a
table_constraint.  The definition of a CHECK table_constraint
does not allow us to specify which column it is for.  The system
is not noticing that only one column is being referenced in the
CHECK expression, and is not associating the constraint with that
column.  That seems to be somewhere between a missing feature and
a bug.  If I had a way to tell the system explicitly which column
the CHECK applies to (e.g. by the system supporting the syntax
    CHECK (expression) FOR colname
as it does when it is a DEFAULT constraint created with ALTER
TABLE ADD CONSTRAINT), it would be a missing feature that it
doesn't notice that the expression only references one column.
However, as I apparently have no way to modify the CHECK
expression text without it losing its column-level-ness, it's
more like a bug.

Is there anything that I can do about this?  Any suggestions?

2. Castor JDO: Connect to MS SQL2000

3. Change table check constraint to column check constraint?

4. SubQuery & Outer Query

5. How to check if primary key constraint exist and drop it if it exist

6. The SQL Paradox

7. Constraint name constraint-name already exists.

8. line feed at the end of my description

9. Check Constraint vs. Foreign Key Constraint

10. Checking Enabled status of Triggers/Constraints

11. check constraint or referential constraint

12. Check constraints over app based constraints

13. Check Constraint vs. Foreign Key Constraint


All times are UTC
Board index