复合PRIMARY KEY对所涉及的列强制执行NOT NULL约束 [英] Composite PRIMARY KEY enforces NOT NULL constraints on involved columns

问题描述

这是Postgres中遇到的一个奇怪的，不必要的行为：
当我创建一个包含复合主键的Postgres表时，它对复合组合的每一列执行NOT NULL约束。

This is one strange, unwanted behavior I encountered in Postgres: When I create a Postgres table with composite primary keys, it enforces NOT NULL constraint on each column of the composite combination.

例如，

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

强制 NOT NULL 列的约束 m_id 和 x_id ，我不想要！
MySQL不这样做。我认为Oracle也不会这样做。

enforces NOT NULL constraint on columns m_id and x_id, which I don't want! MySQL doesn't do this. I think Oracle doesn't do it as well.

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

I understand that PRIMARY KEY enforces UNIQUE and NOT NULL automatically but that makes sense for single-column primary key. In a multi-column primary key table, the uniqueness is determined by the combination.

有没有简单的方法来避免Postgres的这种行为？

如果我这样做：

Is there any simple way of avoiding this behavior of Postgres?
If I do this:

CREATE TABLE distributors (m_id integer, x_id integer);

我没有得到任何 NOT NULL 约束

推荐答案

如果您需要允许NULL值，请使用 UNIQUE 约束 ，而不是一个 PRIMARY KEY （并添加一个代理PK列，我建议一个 serial ）。这允许列为NULL：

If you need to allow NULL values, use a UNIQUE constraint instead of a PRIMARY KEY (and add a surrogate PK column, I suggest a serial). This allows columns to be NULL:

CREATE TABLE distributor (
   distributor_id serial PRIMARY KEY
 , m_id integer
 , x_id integer
 , UNIQUE(m_id, x_id)
);

注意，（每个文档）：

为了唯一约束的目的，空值不被认为是相等的。 >

For the purpose of a unique constraint, null values are not considered equal.

在您的情况下，您可以输入（1，NULL） c>（m_id，x_id）任何次数，而不违反约束。 Postgres从不考虑两个NULL值等于 - 按照SQL标准中的定义。

In your case, you could enter (1, NULL) for (m_id, x_id) any number of times without violating the constraint. Postgres never considers two NULL values equal - as per definition in the SQL standard.

如果您需要处理 NULL 值等于不允许这样的重复，我看到两个选项：

If you need to treat NULL values as equal to disallow such "duplicates", I see two options:

另外到 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，可以快速移除。

相关：

But this gets out of hands quickly with more than two columns that can be NULL.
Related:

• 创建具有空列的唯一约束

• Create unique constraint with null columns

多列UNIQUE索引，而不是UNIQUE约束。我们需要一个免费的默认值，它不会在相关列中出现，例如 -1 。添加 CHECK 约束以禁止它：

Instead of the UNIQUE constraint. We need a free default value that is never present in involved columns, like -1. Add CHECK constraints to disallow it:

CREATE TABLE distributor (
   distributor serial PRIMARY KEY
 , m_id integer
 , x_id integer
 , CHECK (m_id <> -1)
 , CHECK (x_id <> -1)
);

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

某些RDBMS如何处理事物并不总是适当行为的有用指标。 Postgres手册提示：

How certain RDBMS handle things isn't always a useful indicator for proper behavior. The Postgres manual hints at this:

这意味着即使存在唯一的约束条件，也可以
将包含空值的重复行存储在至少一个
受限列。此行为符合SQL标准
但，我们听说其他SQL数据库可能不遵循此规则。
开发旨在便携式的应用程序时要小心。

That means even in the presence of a unique constraint it is possible to store duplicate rows that contain a null value in at least one of the constrained columns. This behavior conforms to the SQL standard, but we have heard that other SQL databases might not follow this rule. So be careful when developing applications that are intended to be portable.

强调我的。

这篇关于复合PRIMARY KEY对所涉及的列强制执行NOT NULL约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！