在PostgreSQL中添加二分之一的非空约束 [英] Adding an one-out-of-two not null constraint in postgresql

问题描述

如果我在Postgresql中有一个表：

If I have a table in 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)
);

我需要进行约束，以使 schoolName 或 studiedAt 不必为空（其中之一必须具有信息）。

I need to make a constraint so that either schoolName or studiedAt needs to not be null (one of them has to have information in it).

如何

推荐答案

您可以使用检查约束，例如

constraint chk_education check (schoolName is not null or studiedAt is not null)

来自手册：

检查约束是最通用的约束类型。它允许您指定某个列中的值必须满足布尔值（真值）表达式。

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.

编辑：遵守Pithyless的解释：

Alternative to comply with Pithyless' interpretation:

constraint chk_education check ((schoolName is not null and studiedAt is null) or (schoolName is null and studiedAt is not null))

这篇关于在PostgreSQL中添加二分之一的非空约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！