复合主键对涉及的列强制实施 NOT NULL 约束

Question

这是我在Postgres遇到的一个奇怪的，不需要的行为：当我使用复合主键创建 Postgres 表时，它会对复合组合的每一列强制实施 NOT NULL 约束。

例如

CREATE TABLE distributors (m_id integer, x_id integer, PRIMARY KEY(m_id, x_id));

对列m_id和x_id强制执行NOT NULL约束，这是我不想要的！MySQL不这样做。我认为甲骨文做得不好。

我知道PRIMARY KEY自动强制执行UNIQUE和NOT NULL，但这对单列主键有意义。在多列主键表中，唯一性由组合确定。

有没有简单的方法可以避免Postgres的这种行为？当我执行这个时：

CREATE TABLE distributors (m_id integer, x_id integer);

当然，我没有得到任何NOT NULL限制。但我也不会有主键。

Answer 1

如果需要

允许 NULL 值，请使用 UNIQUE 约束(或索引(而不是PRIMARY KEY(并添加代理项 PK 列 - 我建议在 Postgres 10 或更高版本中使用serial或IDENTITY列(。

• 自动递增表列

UNIQUE约束允许列为 NULL：

CREATE TABLE distributor (
  distributor_id GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, m_id integer
, x_id integer
, UNIQUE(m_id, x_id)  -- !
-- , CONSTRAINT distributor_my_name_uni UNIQUE (m_id, x_id)  -- verbose form
);

手册：

出于唯一约束的目的，除非指定了 null 值NULLS NOT DISTINCT否则不被视为相等。

在您的情况下，您可以输入类似 (1, NULL) (m_id, x_id) 的次数，而不会违反约束。Postgres 从不认为两个 NULL 值相等 - 根据 SQL 标准中的定义。

如果您需要将NULL值视为相等(即"不明显"(以禁止此类"重复"，我看到两个三个(自 Postgres 15 以来(选项：

0. NULLS NOT DISTINCT

此选项是在 Postgres 15 中添加的，允许将 NULL 值视为"不区分"，因此其中两个在唯一约束或索引中冲突。这是最方便的选择，继续前进。手册：

这意味着即使存在独特的约束，也可以将包含 null 值的重复行存储在至少一个受约束的列。可以通过添加条款 NULLS NOT DISTINCT ...

详细说明：

• 使用空列创建唯一约束

1. 两个部分索引

除了上述UNIQUE约束之外：

CREATE UNIQUE INDEX dist_m_uni_idx ON distributor (m_id) WHERE x_id IS NULL;
CREATE UNIQUE INDEX dist_x_uni_idx ON distributor (x_id) WHERE m_id IS NULL;

但是，对于两列以上的 NULL，这很快就会失控。看：

• 使用空列创建唯一约束

2. 表达式的多列UNIQUE索引

而不是UNIQUE约束。我们需要一个免费的默认值，该值永远不会出现在所涉及的列中，例如 -1 .添加CHECK约束以禁止它：

创建表分发服务器 (   分发器串行主键 、m_id整数 、x_id整数 、检查 (m_id 和 lt> -1( 、检查 (x_id 和 lt> -1();

CREATE UNIQUE INDEX distributor_uni_idx
ON distributor (COALESCE(m_id, -1), COALESCE(x_id, -1));

Answer 2

如果你想要一个多态关系

您的表使用的列名表明它们可能是对其他表的引用：

CREATE TABLE distributors (m_id integer, x_id integer);

因此，我认为您可能正在尝试与其他表建立多态关系模型 - 表中的记录distributors可以引用一个m记录x或一个x记录。

多态关系在 SQL 中很困难。我看到的关于这个主题的最佳资源是"在关系数据库中建模多态关联"。在那里，提供了四个替代选项，大多数情况下的建议称为"独占属于"，在您的情况下，这将导致这样的表格：

CREATE TABLE distributors (
  id serial PRIMARY KEY,
  m_id integer REFERENCES m,
  x_id integer REFERENCES x,
  CHECK (
    ((m_id IS NOT NULL)::integer + (x_id IS NOT NULL)::integer) = 1
  )
);
CREATE UNIQUE INDEX ON distributors (m_id) WHERE m_id IS NOT NULL;
CREATE UNIQUE INDEX ON distributors (x_id) WHERE x_id IS NOT NULL;

与其他解决方案一样，这使用代理项主键列，因为主键强制不包含 SQL 标准中的NULL值。

该解决方案在 Brandstetter @Erwin回答中的三个选项中添加了第 4 个选项，即如何避免"您可以在不违反约束的情况下输入类似 (1, NULL) (m_id, x_id) 次"的情况。 在这里，该案例通过两种措施的组合排除在外：

1. 每列上的部分唯一索引单独：(1, NULL)和(1, NULL)的两条记录不会违反第二列的约束，因为NULL被认为是不同的，但它们会违反第一列的约束(值为 1 的两条记录(。

2. 检查约束：缺少的部分阻止了多个(NULL, NULL)记录，仍然允许，因为NULL被认为是不同的，无论如何，因为我们的部分索引不覆盖它们以节省空间和写入事件。这是通过 CHECK 约束实现的，该约束通过确保只NULL一列来防止任何(NULL, NULL)记录。

但有一个区别：@Erwin Brandstetter 答案中的所有替代方案都允许至少一个记录(NULL, NULL)和任意数量的记录，在任何列中没有NULL值(如(1, 2)(。对多态关系进行建模时，您希望禁止此类记录。这是通过上述解决方案中的检查约束实现的。