多列主键中的空值

Question

我有一个包含多个列的表格组成主键。存储的数据的性质允许其中一些字段具有NULL值。我已经按照以下方式设计了我的表：

CREATE TABLE `test` (
    `Field1` SMALLINT(5) UNSIGNED NOT NULL,
    `Field2` DECIMAL(5,2) UNSIGNED NULL DEFAULT NULL,
    PRIMARY KEY (`Field1`, `Field2`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;

然而，当我运行describe test时，它显示如下：

|| *Field* || *Type*                || *Null* || *Key* || *Default* || *Extra* 
|| Field1  || smallint(5) unsigned  || NO     || PRI   ||           ||         
|| Field2  || decimal(5,2) unsigned || NO     || PRI   || 0.00      ||         

我在插入一个NULL值时一直收到一个错误。

列“Field2”无法为 null

这是因为主键的一部分字段不能为null吗？除了使用像“0”这样的值替代NULL，还有哪些替代方法？

- simbabque

---

多亏了vj shah的链接，@Tomalak提出了一个很好的观点(https://dev59.com/8nRC5IYBdhLWcg3wK92V#386061)：这个限制是基于SQL的基本原则，即由于必须将PRIMARY key的各个部分与每一行进行比较，“NULL不能成为比较的一部分-这样比较的结果总是NULL”，因此强制执行PRIMARY key的唯一性需要非空列。 - Bob Stein

对于字符串，请使用空字符串（或将其设置为默认值）作为其中一列，以便可以插入一条记录而不会出现任何问题。后续的插入需要具有某些值，以确保复合键（多列主键）的唯一性。 - Manohar Reddy Poreddy

Answer 1

来自MySQL文档：

主键

一种唯一索引，其中所有关键列必须定义为NOT NULL。如果它们没有明确声明为NOT NULL，则MySQL会隐式（且悄无声息地）声明它们。表只能有一个主键。主键的名称始终为PRIMARY，因此不能用作任何其他类型索引的名称。

https://dev.mysql.com/doc/refman/8.0/en/create-table.html

如果Field2可以为空，那么我质疑您为什么需要将其作为主键的一部分，因为您随后需要使Field1在所有行中不同。因此，Field1本身应该足以成为主键。您可以在Field2上创建不同类型的索引。

- Girish Rao

---

令人惊讶的是，它允许使用一个被定义为可接受空值的列创建主键。 - Marvo

这就是我一直在寻找但没有找到的定义。谢谢。实际上，我现在有12列组成主键。其中5个值是可空的。这是一种缓存策略，不是所有的值都需要在缓存请求中。我的想法是使用复合主键比使用自增值作为PK并添加一个覆盖所有其他值的唯一索引更便宜。看来我必须走这条路了。 - simbabque

@Girish，我刚遇到了这个问题，并且也想要一个可空字段。在我的情况下，主键是两个字段，其中一个可为空，但我希望最多只有一行第二个字段为空，这样才能成为有效的主键。我认为这是MySQL的规范失败了。 - Seb

我有同样的问题，我需要一个包含一个空值的双列主键。该列还是一个外键，可能链接到另一个表。如果没有空选项，我必须将其设置为一个值，这会破坏外键。 - Tobia

我看不到它。显然，Field1 -> 5 和 Field2 -> null 与 Field1 -> 5 和 Field2 -> 3 是不同的，因此可以用于唯一标识不同的对。我通过谷歌搜索找到了这个帖子，因为我遇到了这种情况，但不明白为什么不能在主键中使用NULL。 - Pere

要更详细地解释为什么唯一键可以而主键不行，请查看viyay答案下链接的问题的第二个答案。 - Matthieu

Answer 2

主键用于使列既唯一又非空

如需插入 NULL 值，请将 field2 设为唯一（UNIQUE）。

唯一约束使字段删除重复项但允许null值

- Lordferrous

---

感谢您的提问，这是答案。接受的回答是错误的。 - singe3

@singe3 好的，问题不是“我如何创建一个带有NULL值的索引”，而是“为什么我不能在主键中有NULL值”。我很高兴独特键可以让你拥有一个NULL字段的信息对你有所帮助，但这并没有回答问题。被接受的答案才是正确的。 - simbabque

从技术上讲，您需要在field1和field2上创建唯一约束/索引，并可能添加一个代理键作为主键（以便您拥有一个）。 - Jeremy Giaco

Answer 3

主键是指列不能有NULL值。因此，用于定义复合主键的列不会有NULL。

此外，Oracle服务器比较在复合主键定义中使用的所有列的组合。如果您所有列的现有数据（例如x、y）与新添加的行匹配，则会引发唯一约束冲突的错误。

此外，请查看此线程：复合主键中可空列有什么问题？。

这个链接提供了关于复合键中可空列可能性的有价值的信息！

- vijay

---

谢谢您的回复。不过我使用的是MySQL，而不是Oracle。 - simbabque

Answer 4

您可以像这样使用唯一键：

mysql> CREATE TABLE `test` (
    ->     `Field1` SMALLINT(5) UNSIGNED NOT NULL,
    ->     `Field2` DECIMAL(5,2) UNSIGNED NULL DEFAULT NULL,
    ->     UNIQUE KEY (`Field1`, `Field2`)
    -> )
    -> COLLATE='latin1_swedish_ci'
    -> ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql> 
mysql> desc test
    -> ;
+--------+-----------------------+------+-----+---------+-------+
| Field  | Type                  | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+-------+
| Field1 | smallint(5) unsigned  | NO   | MUL | NULL    |       |
| Field2 | decimal(5,2) unsigned | YES  |     | NULL    |       |
+--------+-----------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

- Dmitry Kaigorodov

Answer 5

您可以使用唯一键，请参考此链接，它们可以与null值一起使用。 http://www.xaprb.com/blog/2009/09/12/the-difference-between-a-unique-index-and-primary-key-in-mysql/

- jcho360