检查多列约束 [英] CHECK CONSTRAINT on multiple columns

问题描述

我使用SQL Server 2008

I use SQL Server 2008

我在同一张表的多列上使用CHECK CONSTRAINT来尝试验证数据输入.

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

我收到一个错误:

列的列CHECK约束 "AAAA"引用了另一列， 表"XXXX".

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

检查约束不能以这种方式工作.

CHECK CONSTRAINT does not work in this way.

是否可以在不使用FK的情况下在单个表上实现此功能?

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

谢谢

这是我的代码示例

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

推荐答案

是的，在表级别定义CHECK CONSTRAINT

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

您将其声明为列约束

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

当然，问题仍然存在，您是在使用CHECK约束，还是FK约束??

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

这篇关于检查多列约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！