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

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...)