SQL 条件 SQLite 检查约束？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2615477/
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
Conditional SQLite check constraint?
提问by Rezzie
I have a table defined by the following SQL:
我有一个由以下 SQL 定义的表：
CREATE TABLE test (
id integer PRIMARY KEY NOT NULL UNIQUE,
status text NOT NULL,
enddate date,
/* Checks */
CHECK (status IN ("Current", "Complete"))
);
I'd like to add a constraint that requires enddateto be non-null ifthe statusis "Complete".
我想补充一点，需要约束enddate为非空当的status是“完成”。
Is this possible? I am using SQLite v3.6.16.
这可能吗？我正在使用 SQLite v3.6.16。
回答by Andomar
How about:
怎么样：
CHECK (status = "Current" or (status = "Complete" and enddate is not null))
回答by Ensarija
CREATE TABLE test (
id integer PRIMARY KEY,
status text NOT NULL CHECK (status IN ('Current', 'Complete')),
enddate date NOT NULL
);
This will work in SQLite, with the CHECKconstraint written inline. I changed double quotes to apostrophes so it can be used in PHP.
这将在 SQLite 中工作，CHECK约束写入内联。我将双引号更改为撇号，以便它可以在 PHP 中使用。
回答by relatively_random
There's nothing stopping you from having multiple CHECKconstraints on a single table. IMO the simplest and most easily expandable solution:
没有什么可以阻止您CHECK对单个表进行多个约束。IMO 最简单且最易于扩展的解决方案：
CHECK (status IN ("Current", "Complete"))
CHECK (status <> "Complete" OR enddate IS NOT NULL)
This uses the fact that if A then Bis logically equivalent to either not A or B.
这使用的事实是，如果 A then B在逻辑上等同于not A 或 B。
