复合主键对涉及的列强制执行非空约束。

11 浏览
0 Comments

复合主键对涉及的列强制执行非空约束。

PostgreSQL在创建具有复合主键的表时，会对复合组合的每个列强制执行NOT NULL约束。例如，下面的代码：\nCREATE TABLE distributors (m_id integer, x_id integer, PRIMARY KEY(m_id, x_id));\n会对m_id和x_id列强制执行NOT NULL约束，而我不希望这样！MySQL不会这样做，我认为Oracle也不会。\n我理解PRIMARY KEY会自动强制执行UNIQUE和NOT NULL，但这对于单列主键是有道理的。在多列主键表中，唯一性是由组合确定的。\n有没有简单的方法可以避免PostgreSQL的这种行为？当我执行以下代码时：\nCREATE TABLE distributors (m_id integer, x_id integer);\n当然不会有任何NOT NULL约束。但是，我也不会有主键。

0

2 答案

0 Comments

复合主键强制执行涉及列的非空约束的原因是为了解决多态关系的建模问题。在SQL中，多态关系比较困难。在《在关系数据库中建模多态关联》一文中，作者提出了四种备选方案，其中在大多数情况下推荐使用的是“独占归属”。在这种情况下，可以创建一个类似下面的表结构：

CREATE TABLE distributors (

id serial PRIMARY KEY,

m_id integer REFERENCES m,

x_id integer REFERENCES x,

CHECK (

((m_id IS NOT NULL)::integer + (x_id IS NOT NULL)::integer) = 1

)

);

CREATE UNIQUE INDEX ON distributors (m_id) WHERE m_id IS NOT NULL;

CREATE UNIQUE INDEX ON distributors (x_id) WHERE x_id IS NOT NULL;

与其他解决方案类似，这里使用了一个代理主键列，因为根据SQL标准，主键不能包含NULL值。

这个解决方案通过两个措施来排除“可以输入类似于(1, NULL)的(m_id, x_id)”的情况：

1. 分别在每个列上创建部分唯一索引：两个记录(1, NULL)和(1, NULL)不会违反第二列的约束，因为NULL被认为是不同的，但它们将违反第一列的约束（两个记录具有相同的值1）。

2. 使用CHECK约束：防止多个(NULL, NULL)记录，这是允许的，因为NULL被认为是不同的，而且因为部分索引不包括它们，以节省空间和写入事件。这通过CHECK约束实现，确保恰好有一个列是NULL，从而阻止任何(NULL, NULL)记录。

然而，与《Brandstetter的答案》中的其他解决方案有一个区别：所有备选方案都允许至少一个(NULL, NULL)记录，以及任意数量的没有任何列为NULL值的记录（例如(1, 2)）。在建模多态关系时，希望禁止这样的记录。上述解决方案通过CHECK约束来实现这一点。

0
0 Comments

复合主键会对涉及的列强制执行NOT NULL约束。如果需要允许NULL值，则应使用UNIQUE约束（或索引）代替PRIMARY KEY，并添加一个代理主键列-建议在Postgres 10或更高版本中使用serial或IDENTITY列。

解决方法之一是使用UNIQUE约束，允许列为空：

CREATE TABLE distributor (
  distributor_id GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  m_id integer,
  x_id integer,
  UNIQUE(m_id, x_id)
);

在这种情况下，可以多次输入`(1, NULL)`作为`(m_id, x_id)`而不违反约束。根据SQL标准的定义，Postgres从不认为两个NULL值相等。

如果需要将NULL值视为相等（即“不可区分”），以禁止这种“重复”，有以下三种解决方法：

1. 使用`NULLS NOT DISTINCT`选项（从Postgres 15开始可用），将NULL值视为“不可区分”，因此在唯一约束或索引中会发生冲突。这是最方便的选项。具体操作可参考[这里](https://stackoverflow.com/questions/8289100/8289253#8289253)。

2. 使用两个部分索引，除了上述的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的列，这种方法会变得更加复杂。

3. 使用多列表达式的UNIQUE索引，而不是使用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));

MySQL在这方面的行为与Postgres完全相同。该问题中的提问者对其断言是错误的。

总之，以上是在复合主键中强制执行NOT NULL约束的原因以及解决方法的介绍。其中，使用UNIQUE约束（或索引）代替PRIMARY KEY，并添加代理主键列是最常见的解决方法之一。如果需要将NULL视为相等，则可以使用NULLS NOT DISTINCT选项或使用部分索引或多列表达式的UNIQUE索引来实现。

0