I am trying to add a check constraint on sql server 2008. I am getting the following error when i run this code:
alter table db.dbo.myTable add constraint
noWWWifProgramIsKnown check
(dbo.checkcountuniversalservice(A, B,
C)=0 and program='WWW' )
raises error:
The ALTER TABLE statement conflicted with the CHECK constraint "noWWWifProgramIsKnown". The conflict occurred in database "db", table "dbo.tableuniversalservice".
Marc_s' answer on SO says that this is likely b/c some row violates the constraint: The ALTER TABLE statement conflicted
However, no rows violate the constraint because this returns 0:
select COUNT(*) from db.dbo.tableuniversalservice
where
(dbo.checkcountuniversalservice(A, B, C)=0
and
program='WWW'
)
What am I missing?
I think your check is backwards... in order for the constraint to pass, it should evaluate to TRUE
or to UNKNOWN
. Your SELECT
statement is telling you that no rows meet the constraint instead of telling you that no rows violate the constraint.
CHECK constraints reject values that evaluate to FALSE.