CHECK CONSTRAINT on multiple columns

Question

I use SQL Server 2008

I use a CHECK CONSTRAINT on multiple columns in the same table to try to validate data input.

I receive an error:

Column CHECK constraint for column 'AAAA' references another column, table 'XXXX'.

CHECK CONSTRAINT does not work in this way.

Any other way to implement this on a single table without using FK?

Thanks

Here an example of my code

CREATE TABLE dbo.Test 
(   
EffectiveStartDate  dateTime2(2)        NOT NULL,
EffectiveEndDate    dateTime2(2)        NOT NULL
    CONSTRAINT CK_CmsSponsoredContents_EffectiveEndDate CHECK (EffectiveEndDate > EffectiveStartDate),
);

Answer 1

Yes, define the CHECK CONSTRAINT at the table level

CREATE TABLE foo (
   bar int NOT NULL, 
   fred varchar(50) NOT NULL,

   CONSTRAINT CK_foo_stuff CHECK (bar = 1 AND fred ='fish')
)

You are declaring it inline as a column constraint

...
fred varchar(50) NOT NULL CONSTRAINT CK_foo_fred CHECK (...)
...

Edit, easier to post than describe. Fixed your commas.

CREATE TABLE dbo.Test 
(   
  EffectiveStartDate  dateTime2(2)        NOT NULL,
  EffectiveEndDate    dateTime2(2)        NOT NULL,  --need comma
  CONSTRAINT CK_CmsSponsoredContents_EffectiveEndDate CHECK (EffectiveEndDate > EffectiveStartDate) --no comma
);

Of course, the question remains are you using a CHECK constraint where it should be an FK constraint...?

Answer 2

I found it more useful for CONSTRAINT using case statements.

ALTER TABLE dbo.ProductStock  
                           ADD 
                           CONSTRAINT CHK_Cost_Sales   
                           CHECK ( CASE WHEN (IS_NOT_FOR_SALE=0 and SAL_CPU <= SAL_PRICE) THEN 1
                            WHEN (IS_NOT_FOR_SALE=1 ) THEN 1 ELSE 0 END =1 )

Answer 3

You can simply apply your validation in a trigger on the table especially that either way the operation will be rolled back if the check failed.

Answer 4

Check constraints can refer to a single column or to the whole record.

Use this syntax for record-level constraints:

ALTER TABLE MyTable
ADD CONSTRAINT MyCheck
CHECK (...your check expression...)