对一组列的 NOT NULL 约束 [英] NOT NULL constraint over a set of columns

问题描述

我在 Postgres 中有一个表，它当前在 email 列上有一个 NOT NULL 约束.该表还有一个可选的 phone 列.我希望系统接受一些没有 email 的记录，但前提是这些记录有 phone 作为 NOT NULL.换句话说，我需要一个 NOT NULL 数据库约束，以便 CREATE 或 UPDATE 查询成功而没有任何错误，如果 email 或 phone 字段存在.

I have a table in Postgres which currently has a NOT NULL constraint on it's email column. This table also has a phone column which is optional. I would like the system to accept some records without email but only if these have phone as NOT NULL. In other words, I need a NOT NULL database constraint such that CREATE or UPDATE queries succeed without any errors if either or both of email or phone fields are present.

进一步扩展上述内容，是否可以在 Postgres 中指定一组列名，其中一个或多个列名应该 NOT NULL 才能成功更新或创建记录?>

Extending the above further, is it possible in Postgres, to specify a set of column names, one or more of which should be NOT NULL for the record to be successfully updated or created?

推荐答案

@Igor 说得对 和几个OR 的表达式既快速又简单.

@Igor is quite right and a couple of OR'ed expression are fast and simple.

对于一长列列(a、b、c、d、e, f, g 在示例中)，这更短，同样快:

For a long list of columns (a, b, c, d, e, f, g in the example), this is shorter and just as fast:

CHECK (NOT (a,b,c,d,e,f,g) IS NULL)

db<>fiddle 这里
旧的 SQL Fiddle.

以上更详细的形式是:

CHECK (NOT ROW(a,b,c,d,e,f,g) IS NULL)

ROW 在这里是多余的语法.

ROW is redundant syntax here.

使用 IS NULL 测试 ROW 表达式 only 报告 TRUE 如果 每一列 是 NULL - 这恰好是我们想要排除的内容.

Testing a ROW expression with IS NULL only reports TRUE if every single column is NULL - which happens to be exactly what we want to exclude.

不可能简单地用 (a,b,c,d,e,f,g) IS NOT NULL 来反转这个表达式，因为这将测试每一列 IS非空.相反，用 NOT 否定整个表达式.瞧.

It's not possible to simply reverse this expression with (a,b,c,d,e,f,g) IS NOT NULL, because that would test that every single column IS NOT NULL. Instead, negate the whole expression with NOT. Voilá.

手册中的更多详细信息此处 和 此处.

More details in the manual here and here.

一种形式的表达:

CHECK (COALESCE(a,b,c,d,e,f,g) IS NOT NULL)

将实现相同的，不太优雅且有一个主要限制:仅适用于匹配数据类型的列，而对ROW表达式的检查适用于任何列.

would achieve the same, less elegantly and with a major restriction: only works for columns of matching data type, while the check on a ROW expression works with any columns.

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