by chandra ganesa » Wed, 07 Feb 2001 03:41:28
Is this dependent on the way the constraint is created (along with the
column definition ) OR at the end of
the column definition in a CREATE TABLE statement.
I am not exactly sure and a query on the syconstraints shows all Primary
keys as table level constraints however
not all FK's are table level constraints.
Thanks,
Chandra.
1. Column check constraint vs table check constraint
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?
2. ADO - RecordChangeComplete event
3. Changing a table-level constraint to a column-specific constraint
5. Table constraints and Column constraints
6. Ordinal 241 could not be located in ISQLT07C.dll
7. Change table check constraint to column check constraint?
8. The TODO List (Was: Re: Open 7.3 items)
9. CONSTRAINTS - column vs table
10. Constraints: Table vs. Column
11. Check Constraint vs. Foreign Key Constraint
12. Primary key constraints vs Unique constraints
13. Check Constraint vs. Foreign Key Constraint