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

这是我在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不会这样做.我认为Oracle也不会这样做.

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

有没有简单的方法来避免Postgres的这种行为？
如果我执行这个：

CREATE TABLE distributors (m_id integer,x_id integer);

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

如果您需要允许NULL值,请使用 UNIQUE constraint而不是PRIMARY KEY(并添加代理PK列,我建议使用 serial).这允许列为NULL：

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

但请注意(per documentation)：

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

在您的情况下,您可以为(m_id,x_id)输入(1,NULL)任意次数而不违反约束. Postgres从不认为两个NULL值相等 – 根据sql标准中的定义.

如果您需要将NULL值视为等于禁止此类“重复”,我会看到两个选项：

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,则会快速失控.
有关：

> Create unique constraint with null columns

2.表达式的多列UNIQUE索引

而不是UNIQUE约束.我们需要一个在相关列中永远不会出现的自由默认值,例如-1.添加CHECK约束以禁止它：

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

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

某些RDBMS如何处理事情并不总是正确行为的有用指标. Postgres manual hints at this：

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.

大胆强调我的.

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

猜你在找的Postgre SQL相关文章