在 postgresql 中添加二选一的非空约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5447880/
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
Adding an one-out-of-two not null constraint in postgresql
提问by Jimmy
If I have a table in Postgresql:
如果我在 Postgresql 中有一个表：
create table Education (
id integer references Profiles(id),
finished YearValue not null,
started YearValue,
qualification text,
schoolName text,
studiedAt integer references Organizations(id),
primary key (id)
);
I need to make a constraint so that either schoolNameor studiedAtneeds to not be null (one of them has to have information in it).
我需要做一个约束，以便要么schoolName或studiedAt不需要为空（其中一个必须有信息）。
How do I do this?
我该怎么做呢？
回答by Aleksi Yrttiaho
You can use a check constrainte.g.
您可以使用检查约束，例如
constraint chk_education check (schoolName is not null or studiedAt is not null)
From the manual:
从手册：
A check constraint is the most generic constraint type. It allows you to specify that the value in a certain column must satisfy a Boolean (truth-value) expression.
检查约束是最通用的约束类型。它允许您指定某个列中的值必须满足布尔（真值）表达式。
Edit: Alternative to comply with Pithyless' interpretation:
编辑：替代符合 Pithyless 的解释：
constraint chk_education check ((schoolName is not null and studiedAt is null) or (schoolName is null and studiedAt is not null))
回答by Kuberchaun
You can also use a trigger on update and insert to check that a rule is followed before allowing the data into the table. You would normally use this type of approach when the check constraint needs more complicated logic.
您还可以在更新和插入时使用触发器来检查在允许数据进入表之前是否遵循了规则。当检查约束需要更复杂的逻辑时，您通常会使用这种类型的方法。
