by 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?