SQL 在多列上检查约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3438066/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
CHECK CONSTRAINT on multiple columns
提问by GibboK
I use SQL Server 2008
我使用 SQL Server 2008
I use a CHECK CONSTRAINT on multiple columns in the same table to try to validate data input.
我在同一个表中的多个列上使用 CHECK CONSTRAINT 来尝试验证数据输入。
I receive an error:
我收到一个错误：
Column CHECK constraint for column 'AAAA' references another column, table 'XXXX'.
列“AAAA”的列 CHECK 约束引用了另一列，即表“XXXX”。
CHECK CONSTRAINT does not work in this way.
CHECK CONSTRAINT 不能以这种方式工作。
Any other way to implement this on a single table without using FK?
还有其他方法可以在不使用 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),
);
回答by gbn
Yes, define the CHECK CONSTRAINT at the tablelevel
是的，在表级别定义 CHECK CONSTRAINT
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 columnconstraint
您将其内联声明为列约束
...
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...?
当然，问题仍然是您是否在应该是 FK 约束的地方使用了 CHECK 约束......？
回答by devio
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...)
回答by Mostafa Elmoghazi
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.
您可以简单地在表上的触发器中应用您的验证，特别是如果检查失败，操作将回滚。
