I have a table full of data and I have a constraint on a field in that table that is setup to ensure the total of 2 fields equals a 3rd field. For example:
ALTER TABLE [dbo].[Table1] WITH NOCHECK ADD CONSTRAINT [CHK_TOTALS] CHECK ([Field3] = [Field1] + [Field2])
If I then check this constraint:
ALTER TABLE [dbo].[Table1] CHECK CONSTRAINT [CHK_TOTALS]
No errors are returned, the operation executes successfully, therefore it is as though all rows are satisfying the constraint.
However, if I then execute
DBCC CHECKCONSTRAINTS([Table1]) several rows are returned for constraint
CHK_TOTALS showing that there are multiple breaches of the constraint.
I have checked the data and can confirm that
[Field1] + [Field2] does not total
[Field3] for all rows.
Why does the first check not identify these but using
DBCC CHECKCONSTRAINTS does?