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

问题描述

我在Postgres中有一个表，它目前对它的电子邮件列有 NOT NULL 约束。此表还有一个 phone 列，这是可选的。我想系统接受一些没有电子邮件的记录，但只有这些电话为 NOT NULL 。换句话说，我需要一个 NOT NULL 数据库约束，使得 CREATE 或 UPDATE 如果电子邮件或电话字段中的任一个或两者都存在，则查询成功而没有任何错误。进一步扩展上面的内容，在Postgres中可以指定一组列名，其中一个或多个应该 NOT NULL 以便成功更新或创建记录？

解决方案

@ Igor是对的，和一对夫妇OR'ed表达式是简单和快速。

列（ a ， b ， c ， d ， e ， f ， g 在示例中），可以使用这个更短（也快）的形式：

  CHECK （a，b，c，d，e，f，g）IS $）
  

- > SQLfiddle演示。

更详细的形式是：

  CHECK（NOT ROW（a，b，c，d，e，f ，g）IS NULL）
  

这如何工作？

使用 IS NULL 测试ROW表达式报告 TRUE if 每个列 IS NULL 。

不能使用（a，b，c，d）来颠倒这个表达式，e，f，g）IS NOT NULL ，因为这将测试每个单列 is NOT NULL 。相反，使用 NOT 取消整个表达式。 Voilá。

手册中的更多详情此处和此处。

以下形式的表达式：

  CHECK（COALESCE ，c，d，e，f，g）是否为非空）
  

优雅且具有主要限制：仅适用于匹配类型的列，而上述ROW表达式可用于任何列。

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.

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 is quite right and a couple of OR'ed expression are simple and fast.

For long lists of columns (a, b, c, d, e, f, g in the example), one could use this shorter (and also fast) form:

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

-> SQLfiddle demo.

A more verbose form of the same would be:

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

How does this work?

Testing a ROW expression with IS NULL only reports TRUE if every single column IS NULL. That's exactly what we want to test for (and exclude).

It's not possible to 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.

An expression of the form:

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

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

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