by Shevkar, Mukun » Sun, 31 Dec 1899 09:00:00
I am trying to put check constraint on date column but it returns following
err
Error 201 : general syntax error
check constraint reads as - trans_date >= today
Can u help me put this type of check constraint on date column?
Thanks
______________________________________________________________
what is achieved ,
was not aimed for !
by Art S. Kage » Sun, 31 Dec 1899 09:00:00
> I am trying to put check constraint on date column but it returns following
> err
> Error 201 : general syntax error
> check constraint reads as - trans_date >= today
> Can u help me put this type of check constraint on date column?
Art S. Kagel
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. Table Locked on DisconnectedRecordset call
3. Change table check constraint to column check constraint?
4. Set role command - security problem
5. Check Constraint vs. Foreign Key Constraint
6. Measure with Distinct Count
7. check constraint or referential constraint
8. Unable to validate/drop object
9. Check constraints over app based constraints
10. Check Constraint vs. Foreign Key Constraint
11. Check constraint w UDF on Update doesnt check
12. DELETE does not check CHECK constraint
13. Does anyone know why CHECK Constraint text does not appear