postgresql 复合 PRIMARY KEY 对涉及的列强制执行 NOT NULL 约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26642835/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Composite PRIMARY KEY enforces NOT NULL constraints on involved columns
提问by user3422637
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.
这是我在 Postgres 中遇到的一种奇怪的、不需要的行为：当我创建一个带有复合主键的 Postgres 表时，它对复合组合的每一列强制执行 NOT NULL 约束。
For example,
例如，
CREATE TABLE distributors (m_id integer, x_id integer, PRIMARY KEY(m_id, x_id));
enforces NOT NULLconstraint on columns m_idand x_id, which I don't want!
MySQL doesn't do this. I think Oracle doesn't do it as well.
NOT NULL对列m_id和强制执行约束x_id，这是我不想要的！MySQL 不会这样做。我认为 Oracle 也没有这样做。
I understand that PRIMARY KEYenforces UNIQUEand NOT NULLautomatically but that makes sense for single-column primary key. In a multi-column primary key table, the uniqueness is determined by the combination.
我知道这会自动PRIMARY KEY强制执行UNIQUE，NOT NULL但这对单列主键很有意义。在多列主键表中，唯一性由组合决定。
Is there any simple way of avoiding this behavior of Postgres?
If I execute this:
有什么简单的方法可以避免 Postgres 的这种行为吗？
如果我执行这个：
CREATE TABLE distributors (m_id integer, x_id integer);
I do not get any NOT NULLconstraints of course.
我NOT NULL当然没有任何限制。
回答by Erwin Brandstetter
If you needto allow NULL values, use a UNIQUEconstraintinstead of a PRIMARY KEY(and add a surrogate PK column, I suggest a serial). This allows columns to be NULL:
如果您需要允许 NULL 值，请使用UNIQUE约束而不是 a PRIMARY KEY（并添加代理 PK 列，我建议使用 a serial）。这允许列为 NULL：
CREATE TABLE distributor (
distributor_id serial PRIMARY KEY
, m_id integer
, x_id integer
, UNIQUE(m_id, x_id)
);Note, however (per documentation):
但是请注意（根据文档）：
For the purpose of a unique constraint, null values are not considered equal.
出于唯一约束的目的，空值不被视为相等。
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.
在你的情况，你可以输入(1, NULL)为(m_id, x_id)不违反约束任意次数。Postgres 从不认为两个 NULL 值相等- 根据 SQL 标准中的定义。
If you need to treat NULLvalues as equal to disallow such "duplicates", I see two options:
如果您需要将NULL值视为相等以禁止此类“重复”，我会看到两个选项：
1. Two partial indexes
1. 两个部分索引
In additionto the UNIQUEconstraint above:
除了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;
But this gets out of hands quickly with more than two columns that can be NULL.
Related:
但是，如果超过两列可以为 NULL，这很快就会失控。
有关的：
2. A multi-column UNIQUE index on expressions
2. 表达式上的多列 UNIQUE 索引
Instead of the UNIQUE constraint. We need a free default value that is never present in involved columns, like -1. Add CHECKconstraints to disallow it:
而不是 UNIQUE 约束。我们需要一个永远不会出现在相关列中的免费默认值，例如-1. 添加CHECK约束以禁止它：
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))
How certain RDBMS handle thingsisn't always a useful indicator for proper behavior. The Postgres manual hints at this:
某些 RDBMS 如何处理事物并不总是正确行为的有用指标。在Postgres的本手册的提示：
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.
这意味着即使存在唯一约束，也可以在至少一个受约束的列中存储包含空值的重复行。这种行为符合 SQL 标准，但我们听说其他 SQL 数据库可能不遵循此规则。因此，在开发可移植的应用程序时要小心。
Bold emphasis mine.
大胆强调我的。
