复合 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(m_id, x_id));

对列 m_id 和 x_id 强制执行 NOT NULL 约束，我不想要！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 execute this:

CREATE TABLE distributors (m_id integer, x_id integer);

我当然没有得到任何 NOT NULL 约束.

I do not get any NOT NULL constraints of course.

推荐答案

如果您需要允许 NULL 值，请使用 UNIQUE 约束 而不是 PRIMARYKEY(并添加代理PK列，我建议使用串行).这允许列为 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.

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

In your case, you could enter something like (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. See:

• 使用空列创建唯一约束

代替 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屋！