Column check constraint vs table check constraint

Column check constraint vs table check constraint

Post by J. Merril » Tue, 27 Aug 2002 21:19:06



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?

 
 
 

Column check constraint vs table check constraint

Post by Ron Talmag » Tue, 27 Aug 2002 23:45:18


J.

This appears to be fixed in SQL Server 2000 (SP2). The second
sp_helpconstraint shows it as a column constraint also, unlike SQL Sever
7.0.

Ron
--
Ron Talmage
SQL Server MVP


Quote:> 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?


 
 
 

Column check constraint vs table check constraint

Post by Dan Guzma » Wed, 28 Aug 2002 02:16:50


To add to Ron's response, I don't believe there is a functional
difference between a column level and table level check constraint.  In
either case, the specified data integrity rule is applied to the column
and the end result is the same.

Apparently, SQL Server 2000 is smarter in this area and automatically
converts a table level check constraint to a column level when the
constraint refers to a single column.  No special syntax is necessary.

Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy  Neil Pike):

http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------


Quote:> 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?

 
 
 

1. Change table check constraint to column check constraint?

After our SQL 6.5 database was wiz'd into SQL 7.0 format, I noticed that many
(but not all) of the column check constraints were converted to table check
constraints.  These really are simple data-entry checks, and logically belong
with the column.

Is there a way to convert them back to column check constraints without
having to recreate the table?  I can't find any syntax in ALTER TABLE to add
a column check constraint.

Why do I care?  It's either change the ERWin schema or fix the database, and
frankly, I think ERWin is right this time.

TIA
--
Bobb Head

Sent via Deja.com http://www.deja.com/
Before you buy.

2. Clone a merge-subscriber

3. Check Constraint vs. Foreign Key Constraint

4. SQLMail

5. populating a lstbox from the remainder of a dynaset

6. Problem with Alter Table Constraint NO CHECK/CHECK?

7. Precision Problem in CRecordSet RFX_Date

8. Table constraints vs column constraints

9. Table level column reference problem in Check constraint

10. Find check constraints given Table and Column

11. checking the violation of constraint with existing data (after enabling of the constraint)

12. check constraint or referential constraint