Postgresql 复合 PRIMARY KEY 对涉及的列强制执行 NOT NULL 约束

发布于10月30日

这是我在博士后中遇到的一种奇怪的、不受欢迎的行为:

例如

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

对m_id列和x_id列强制执行NOT NULL约束，这是我不想要的！

我知道PRIMARY KEY会自动执行UNIQUE和NOT NULL，但这对于单列主键来说是有意义的.在多列主键表中，唯一性由组合决定.

有什么简单的方法可以避免博士后的这种行为吗

CREATE TABLE distributors (m_id integer, x_id integer);

当然，我没有得到任何NOT NULL条限制.

推荐答案

如果要允许空值，请使用UNIQUE constraint而不是PRIMARY KEY(并添加一个代理PK列，我建议使用serial).这允许列为空:

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

Note，但是(per documentation):

对于唯一约束，空值不被视为相等.

在您的情况下，您可以在不违反约束的情况下多次输入(1, NULL)或(m_id, x_id).Postgres从不考虑两个空值equal——根据SQL标准中的定义.

如果需要将NULL个值视为等于不允许此类"重复"，I see two options:

1.两个部分索引

In addition到上面的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，那么这个问题很快就会失控.见:

Create unique constraint with null columns

2. A multi-column `UNIQUE` index on expressions

而不是唯一的约束.我们需要一个在相关列中永远不存在的自由默认值，比如-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 things并不总是正确行为的有用指标.Postgres manual hints at this人: