Board index MS SQL Server

Table level column reference problem in Check constraint

Table level column reference problem in Check constraint

Post by willia.. » Tue, 22 Aug 2000 04:00:00



--Im trying to write a constraint that checks the value of another
column in the same table.
--This is what the book seems to want
create table box
        (box_nu smallint primary key,
        Box_sent_date smalldatetime
        constraint ck_sent_date
        check ((Box_sent_date) <= getdate()),
        Box_received_date smalldatetime
        constraint ck_received_date
        check (box_received_date > box_sent_date))
--This is the message I get
Server: Msg 8141, Level 16, State 1, Line 1
Column CHECK constraint for column 'Box_received_date' references
another column, table 'box'.

 --This works when creating the table
        constraint ck_received_date
        check (box_received_date > ('box.box_sent_date')))
--but if I try to  insert data I get the following message
insert box values(1, 9/9/1999,9/19/1999)
Syntax error converting character string to smalldatetime data type.

--What am I doing wrong Thanks in advance

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

 
 
 
Top

Table level column reference problem in Check constraint

Post by Kalen Delane » Tue, 22 Aug 2000 04:00:00


If you want a check constraint to reference another column in the table, you
must define it as a table level constraint and not as part of the column
definition. Your table definition can be made of column defintions (which
can include column level constraint) and of constraints outside of a column
definition, as a separate item in the table list. You must separate this
multicolumn constraint from the rest of the table definition with a comma:

 create table box
 (box_nu smallint primary key,
 Box_sent_date smalldatetime
 constraint ck_sent_date
 check ((Box_sent_date) <= getdate()),  -- this is column level constraint
on a single column; it could be
                                                                     -- on
the same line asa the smalldatetime
 Box_received_date smalldatetime,   -- put a comma here to make the
following contraint be a separate element
 constraint ck_received_date
 check (box_received_date > box_sent_date))

As far as the insert error, SQL Server will interpret 9/9/1999 as a division
problem unless you put quotes around it. All datetime constants must be in
quotes.

HTH

--
Kalen Delaney
MCSE, SQL Server MCT, MVP
www.InsideSQLServer.com
Feed Someone for Free Today:
     www.TheHungerSite.com


> --I'm trying to write a constraint that checks the value of another
> column in the same table.
> --This is what the book seems to want
> create table box
> (box_nu smallint primary key,
> Box_sent_date smalldatetime
> constraint ck_sent_date
> check ((Box_sent_date) <= getdate()),
> Box_received_date smalldatetime
> constraint ck_received_date
> check (box_received_date > box_sent_date))
> --This is the message I get
> Server: Msg 8141, Level 16, State 1, Line 1
> Column CHECK constraint for column 'Box_received_date' references
> another column, table 'box'.

>  --This works when creating the table
> constraint ck_received_date
> check (box_received_date > ('box.box_sent_date')))
> --but if I try to  insert data I get the following message
> insert box values(1, 9/9/1999,9/19/1999)
> Syntax error converting character string to smalldatetime data type.

> --What am I doing wrong - Thanks in advance

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


 
 
 
Top

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. Replication Security

3. Change table check constraint to column check constraint?

4. Display Database Structure?

5. Changing a table-level constraint to a column-specific constraint

6. US-NJ: Englewood-Delphi XML SOAP Oracle Programmer/Analyst

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

8. US-CA-Oracle DBA w/hot Internet Company

9. Table level check constraint

10. Table & Column level constraints

11. Find check constraints given Table and Column

12. Can a column CHECK references another column ?

13. Table level vs. column level check constraints...


All times are UTC
Board index