oracle - 多列的唯一性约束导致空值问题

Question

根据oracle文档，null不能等于或不等于任何值或另一个null

这在任何列上存在唯一性约束的情况下都很明显。但如果唯一性约束在多个列上，则行为会有所不同。例如:

CREATE TABLE table1 (
    col1 NUMBER(2),
    col2 NUMBER(2),
    CONSTRAINT uniq_col1_col2 UNIQUE (col1, col2)
);
INSERT INTO table1 VALUES (1, NULL);
INSERT INTO table1 VALUES (1, NULL);
# ORA-00001: unique constraint (XYZ.UNIQ_COL1_COL2) violated

为什么会这样呢？如何指定忽略空值的约束？

编辑

更具体地说，如果行 (null), (null) 是唯一的，为什么 (1,null), (1,null) 不唯一？这背后的理由是什么？

Answer 1

这就是the documentation says (强调):

To satisfy a unique constraint, no two rows in the table can have the same value for the unique key. However, the unique key made up of a single column can contain nulls. To satisfy a composite unique key, no two rows in the table or view can have the same combination of values in the key columns. Any row that contains nulls in all key columns automatically satisfies the constraint. However, two rows that contain nulls for one or more key columns and the same combination of values for the other key columns violate the constraint.

它正在做它应该做的事情。对于两个示例插入，两个(潜在)行在一个键列中都包含 null，而在另一个键列中包含相同的值 (1)，因此违反了约束。

除此之外没有什么真正有意义的；无论如何，允许两个插入继续都会留下两个无法区分的行。

<小时/>

你问:

More specifically, if rows (null), (null) are unique, why are (1,null), (1,null) not unique? What is the rationale behind this?

因为没有其他键列来强制唯一性。

正如你所说，null 不等于也不等于任何东西。如果您的唯一键仅位于 col1 上，并且您有两行将其设置为 null(这是允许的)，则查询 where col1 is null 将找到这两个行 - 这是可以的因为 is null 与平等无关。您可以说两行都符合条件，但不能说它们等于 null。对于两列键，等效项是其中 col1 = 1 且 col2 为 null。现在平等确实开始发挥作用。

在这两种情况下，空值都会被忽略，剩下的任何内容仍然必须是唯一的。对于单列键，没有其他方法可以强制唯一性。对于两列键，如果 col2 为 null，则 col1 仍然可以进行比较，并且它本身必须是唯一的、有效的。

您还可以执行以下操作:

INSERT INTO table1 VALUES (null, null);
INSERT INTO table1 VALUES (null, null);

同样的情况也适用；它们的空值实际上被忽略了，但是现在 - 与单列键一样 - 没有什么可以强制执行唯一性。