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!
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]]]
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.