如何在多个可空列上创建唯一索引约束？

Question

如何在多个可空列上创建唯一索引约束？

7

如果我使用NULLABLE列设置唯一键索引，我可以插入任意多行：

create table routes (
   id bigint(20) NOT NULL AUTO_INCREMENT,
   firstname varchar(255) NOT NULL,
   lastname varchar(255) NOT NULL,
   departure DATE DEFAULT NULL,
   returndate DATE DEFAULT NULL,
   PRIMARY KEY (id),
   UNIQUE KEY uniq (firstname, lastname, departure)
)

为什么？更重要的是，如何在日期列上保持唯一约束，即使它们可能为空（并且必须保持 DATE 类型）？

以下 SQL 语句可以多次执行：

INSERT INTO `routes` (`firstname`, `lastname`, `departure`, `returndate`) 
       VALUES (NULL, 'john', 'doe', NULL, NULL);

- membersound

你可以分享一下你要插入的实际数据吗？ - Tim Biegeleisen

以上已添加示例。 - membersound

你的唯一键定义中提到了一个名为“origin”的列，但在你的模式或示例中并没有出现？ - Bridge

抱歉，这只是一个类型错误，当然它是引用现有的列。 - membersound

在插入数据时跳过id列。（让数据库管理系统处理AUTO_INCREMENT列。） - jarlh

2个回答

1

这取决于数据库。一些数据库允许在具有唯一约束的列上重复使用NULL，而另一些则不允许。

当然，其中一个选项是使用触发器。但这很繁琐。

另一个选项是使用默认值，并将该列从任何insert中省略，以便使用默认值：

create table routes (
   id bigint(20) NOT NULL AUTO_INCREMENT,
   firstname varchar(255) NOT NULL,
   lastname varchar(255) NOT NULL,
   departure DATE NOT NULL DEFAULT '1900-01-01',
   returndate DATE DEFAULT NULL,
   PRIMARY KEY (id),
   UNIQUE KEY uniq (firstname, lastname, departure)
);

INSERT INTO `routes` (`firstname`, `lastname`, `returndate`) 
       VALUES ('john', 'doe', NULL);

这里有一个db<>fiddle。

- Gordon Linoff

3

据我所知，微软 SQL Server 是唯一一个会让 NULL 在唯一键冲突的数据库。这与 SQL 标准相反。 - Bill Karwin

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Bill Karwin · Accepted Answer

为什么？因为NULL = NULL不成立。

唯一性意味着它不允许另一行在列中具有相同的值。但是在SQL中，将NULL与任何其他值（包括另一个NULL）进行比较都是“未知的”。这就是三值布尔逻辑的定义。

https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-unique说：

对于可以包含NULL的列，唯一索引允许多个NULL值。

如果您执行两次INSERT语句，您希望发生什么？即使“重复项”是NULL，它是否应该导致重复键冲突？

您需要创建一个函数索引（适用于MySQL 8.0.13或更高版本）：

mysql> CREATE TABLE `routes` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `firstname` varchar(255) NOT NULL,
  `lastname` varchar(255) NOT NULL,
  `departure` date DEFAULT NULL,
  `returndate` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq` (`firstname`,`lastname`,(coalesce(`departure`, '1900-01-01')))
);

mysql> INSERT INTO `routes` (`id`, `firstname`, `lastname`, `departure`, `returndate`) 
    -> VALUES (NULL, 'john', 'doe', NULL, NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `routes` (`id`, `firstname`, `lastname`, `departure`, `returndate`) 
    ->  VALUES (NULL, 'john', 'doe', NULL, NULL);
ERROR 1062 (23000): Duplicate entry 'john-doe-1900-01-01' for key 'uniq'

关于您的评论：不，这个函数式索引中的coalesce()不会影响列中存储的数据值，它只会影响被索引的内容。

mysql> select * from routes;
+----+-----------+----------+-----------+------------+
| id | firstname | lastname | departure | returndate |
+----+-----------+----------+-----------+------------+
|  1 | john      | doe      | NULL      | NULL       |
+----+-----------+----------+-----------+------------+

如果您使用MySQL 5.7+，可以进行类似的操作，但是您必须创建一个虚拟列用于索引：

CREATE TABLE `routes` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(255) NOT NULL,
  `lastname` varchar(255) NOT NULL,
  `departure` date DEFAULT NULL,
  `departure_notnull` date GENERATED ALWAYS AS (coalesce(`departure`, '1900-01-01')) VIRTUAL,
  `returndate` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq` (`firstname`,`lastname`,`departure_notnull`)
)

如果您使用MySQL 5.6或更早版本，则无法使用虚拟列或函数索引。您将需要使该列为NOT NULL，并使用特殊值表示它不是日期。