CODEWITHSUNDEEP
sqlsql-servercheck-constraint
GibboK
GibboK

Reputation: 73918

CHECK CONSTRAINT on multiple columns

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),
);

Upvotes: 31

Views: 60788

Answers (4)

Imamul Karim Tonmoy
Imamul Karim Tonmoy

Reputation: 584

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 )

Upvotes: 1

Mostafa Elmoghazi
Mostafa Elmoghazi

Reputation: 2154

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.

Upvotes: 1

gbn
gbn

Reputation: 432261

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

Upvotes: 61

devio
devio

Reputation: 37215

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

Upvotes: 2

Related Questions