question

eghetto avatar image
eghetto asked

Constraint is still "not trusted" after "ALTER TABLE [dbo].[X] WITH CHECK CHECK CONSTRAINT [CK_X_MyConstraint]"

Dear folks, My constraint is still "not trusted" after i run the following statement: `ALTER TABLE [dbo].[X] WITH CHECK CHECK CONSTRAINT [CK_X_MyConstraint]"` `SELECT name, is_disabled, is_not_trusted FROM sys.check_constraints WHERE is_not_trusted = 1` shows it as `is_disabled = 0` and `is_not_trusted = 1`! I checked the data manually - it's valid! Whats wrong? Thanks!
sql-server-2005constraint
7 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sacred Jewel avatar image Sacred Jewel commented ·
Try ALTER TABLE [dbo].[X] CHECK CONSTRAINT [CK_X_MyConstraint] i.e. without "WITH CHECK"
0 Likes 0 ·
eghetto avatar image eghetto commented ·
tried it: no effect :(
0 Likes 0 ·
Sacred Jewel avatar image Sacred Jewel commented ·
what is the check constraint DDL?
0 Likes 0 ·
eghetto avatar image eghetto commented ·
Constraint DDL: `ALTER TABLE [dbo].[X] WITH CHECK ADD CONSTRAINT [CK_X_MyConstraint] CHECK NOT FOR REPLICATION ((ColumnY>=(0) AND ColumnY
0 Likes 0 ·
Sacred Jewel avatar image Sacred Jewel commented ·
Looking at this I beleive a BIT datatype is what required :)
0 Likes 0 ·
Show more comments
eghetto avatar image
eghetto answered
I got it now! If I drop and recreate **without NOT FOR REPLICATION**: `ALTER TABLE [dbo].[X] WITH CHECK ADD CONSTRAINT [CK_X_MyConstraint] CHECK (ColumnY BETWEEN 0 AND 1)` the result of `is_not_trusted` is **0 (FALSE)**! `OBJECTPROPERTY(OBJECT_ID('CK_X_MyConstraint'),'CnstIsNotTrusted')` and `sys.check_constraints ` confirms this. It might be related to [ http://connect.microsoft.com/SQLServer/feedback/details/356183/check-constraint-is-not-enforced-if-marked-not-for-replication][1] Thanks everybody! :) Lesson learned. [1]: http://connect.microsoft.com/SQLServer/feedback/details/356183/check-constraint-is-not-enforced-if-marked-not-for-replication
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image Magnus Ahlkvist commented ·
That makes sense - if not enforced in replication, a constraint can't really be trusted at all.
1 Like 1 ·
robbin avatar image robbin commented ·
There is a tick mark below the downvote button for this answer. Click that. :)
1 Like 1 ·
eghetto avatar image eghetto commented ·
Well who thinks of replication at all...? But how can we mark this issue/question as "solved"/"answered"?
0 Likes 0 ·
Sacred Jewel avatar image Sacred Jewel commented ·
Oh no...I got carried away. I asked for the DDL and never looked it at it. Instead start talking about the BIT column. Damn that crazy CHECK constarint :)
0 Likes 0 ·
Magnus Ahlkvist avatar image Magnus Ahlkvist commented ·
@eghetto - I think you should mark your own answer as the correct one.
0 Likes 0 ·
Show more comments
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
Your constraint says the column must be either 0, 1 or NULL. A bit-datatype can be 0, 1 or NULL. So I agree with @sacred jewel - you could just as well use a bit-datatype instead of a tinyint. But there might of course be reasons to keep it a tinyint - when incoming data is not adhering to the constraint and you still want to be able to take the data in, clean it and then enable the constraint is one scenario. Your CHECK constraint could be simplified. If the column is declared as nullable, NULL is allowed. That doesn't have to be explicitly expressed in a CHECK constraint. **ColumnY>=0 AND ColumnY]]]
8 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

eghetto avatar image eghetto commented ·
Thanks Magnus, but I guess it must be `ALTER TABLE [dbo].[X] WITH CHECK ADD CONSTRAINT [CK_X_MyConstraint] CHECK NOT FOR REPLICATION ((ColumnY BETWEEN 0 AND 1) OR ColumnY IS NULL)`. Do you agree? Let me try this. Indeed it still a mystery why "is_not_trusted" is TRUE...
0 Likes 0 ·
eghetto avatar image eghetto commented ·
Interesting: `((ColumnY BETWEEN 0 AND 1) OR ColumnY IS NULL)` is translated by SQL Server to `((ColumnY>=(0) AND ColumnY
0 Likes 0 ·
eghetto avatar image eghetto commented ·
`SELECT * FROM dbo.X WHERE ColumnY IS NOT NULL AND ColumnY NOT BETWEEN 0 AND 1` returns 0, 1 and NULL...
0 Likes 0 ·
Sacred Jewel avatar image Sacred Jewel commented ·
@Magnus...I think you wanted to refer me instead of @eghetto in your answer.
0 Likes 0 ·
Magnus Ahlkvist avatar image Magnus Ahlkvist commented ·
@sacred jewel - you are absolutely right, sorry!
0 Likes 0 ·
Show more comments
Sacred Jewel avatar image
Sacred Jewel answered
Ok. I think may be is_not_trusted column is not updated correctly. I would search if there is any connect item for that. But you can check the trusted constraints by using this. SELECT OBJECTPROPERTY(OBJECT_ID('constraint'),'CnstIsNotTrusted') This should give you the real picture.
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

eghetto avatar image eghetto commented ·
Nice idea, but `SELECT OBJECTPROPERTY(OBJECT_ID('CK_X_MyConstraint'),'CnstIsNotTrusted')` returns also 1 (TRUE)...
0 Likes 0 ·
Sacred Jewel avatar image Sacred Jewel commented ·
which edition of SQL Server (service Pack) you are on?
0 Likes 0 ·
Sacred Jewel avatar image Sacred Jewel commented ·
On my server, every check constraint (only have two) is shown trusted by "sys.check_constraints", and OBJECTPROPERTY is showing it correctly as well.
0 Likes 0 ·
eghetto avatar image eghetto commented ·
My @@Version `Microsoft SQL Server 2005 - 9.00.5292.00 (Intel X86) Apr 13 2011 15:56:31 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)`
0 Likes 0 ·
Sacred Jewel avatar image Sacred Jewel commented ·
Are you sure you run the command of CHECK CONSTRAINT again?
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.