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

Milner

Why "without FK" ?? Foreign keys are designed to link up two tables - that's their core competency, their job, their reason to be - why not use FK when it's really an FK's job??? – Ladle

I added my code to my question, hope now is make more sense. – Milner

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

Lundeen

hi i tested your code and his working, I posted my on my original question because it is very similar but does not work. Any ideas? thanks for your time – Milner

+1 though I'd argue that logically the constraint is defined at the row level. A table-level CHECK constraint would be able to reference data in different rows in the same table, something SQL Server does not support directly. – Electrophotography

@onedaywhen: A CK constraint is always per row. "table level" refers to attached to the table, rather then attached to a column. – Lundeen

@onedaywhen: Please see msdn.microsoft.com/en-us/library/ms189862.aspx "Column and Table Constraints " – Lundeen

"logically the constraint is defined at the row level" -- note the word "logically". You can logically have constraints at every level: (column, domain, row, table, schema, database, enterprise, global). The SQL Server documentation reflects the fact that the product only supports CHECK constraints at two physical levels, referred to as 'column' and 'table' but logically are column-level and row-level respectively. – Electrophotography

@gbn: you are from a MS Access background, right? In the old days there were Column Validation Rules and Table Validation Rules, the latter thus named because it was defined using the Table Properties (Jet SQL DDL didn't cover Validation Rules). Then for the Access 2000 release, when the Jet engine was being developed by SQL Server team, 'CHECK` constraints were introduced which, unlike SQL Server's own CHECK constraints, whose search conditions could contain a subquery i.e. could refer to rows in the same table or even in another table. But there were problems... – Electrophotography

...Folk who had refereed to the Table Validation Rule as a "table-level validation rule" had missed the point logically i.e. they are logically row-level because they can only refer to values in columns in the same row of the same table. What to call a CHECK constraint that is truly table-level?! The second problem is that the SQL Server team did not implement SQL-92 DEFERRABLE functionality so they are checked INITIALLY IMMEDIATE. That's OK but they are checked per row, rather than per SQL statement which violates the Standard and is very bad news in practise. – Electrophotography

...Back in SQL Server land, a CHECK constraint is always checked per row. So, again, this is a violation of Standards but is OK in practise because logically the 'highest' level they can be defined at is row-level. Actually, you can reveal a problem by including a scalar function that operates on more than row in a CHECK constraint's search condition (more details here: consultingblogs.emc.com/davidportas/archive/2007/02/19/…). – Electrophotography

@onedaywhen: no, SQL Server always. The scalar/udf CHECK thing is well known to me. I can't comment on standards or access but CHECK is always per row in SQL Server (even at the column level it's per row). Column/table level refers to where in the DDL it's defined. AFAIK Jet and SQL Server have always been separate: unless you have other knowledge... And DEFERRABLE is dangerous frankly unless it canbe applied only at the TXN scope. – Lundeen

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

Nomography

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.

Miter

Answer 4

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 )

Spirituality