时间戳

首页 > 解决方案 > MySQL - 唯一的“每个 FK”，可为空的列

mysql - MySQL - 唯一的“每个 FK”，可为空的列

问题描述

我有下表：

CREATE TABLE `Firmware_port_set` (
 `Id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `firmware_id` int(10) unsigned NOT NULL,
 `address` int(10) unsigned NOT NULL,
 `set_id` int(10) unsigned NOT NULL,
 `order_in_firmware` int(10) unsigned DEFAULT NULL,
 PRIMARY KEY (`Id`),
 UNIQUE KEY `firmware_id` (`firmware_id`,`address`),
 KEY `fk_fps_set` (`set_id`),
 KEY `fk_fps_firmware` (`firmware_id`),
 CONSTRAINT `fk_fps_firmware` FOREIGN KEY (`firmware_id`) REFERENCES `Firmware` (`Id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
 CONSTRAINT `fk_fps_set` FOREIGN KEY (`set_id`) REFERENCES `Port_set_template` (`Id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=480 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

其中列order_in_firmware是排序的可选规范。如果此列为空，则将根据地址列进行排序 - 基本上，查询是..ORDER BY COALESCE(order_in_firmware, address)

以下是数据示例：

Id, firmware_id, address, set_id, order_in_firmware
"461","32","105","13",NULL
"462","32","185","13",NULL
"463","32","265","13",NULL
"464","34","36","14","8"
"465","34","44","14","9"
"466","34","52","14","10"
"467","34","60","14","11"
"468","34","68","14","12"
"469","34","76","14","13"
"470","34","84","14","14"
"471","34","92","14","15"
"472","34","100","15","0"
"473","34","114","15","1"
"474","34","128","15","2"
"475","34","142","15","3"
"476","34","156","15","4"
"477","34","170","15","5"
"478","34","184","15","6"
"479","34","198","15","7"

如何创建一个约束来强制order_in_firmware列的值是唯一的，但只有唯一的“每个 FK 值”（firmware_id）并且同时仍然可以为空？

标签： mysql

解决方案

使“其他”列的对具有order_in_firmware唯一性。

CREATE TABLE `firmware_port_set`
             (...
              UNIQUE (`firmware_id`,
                      `order_in_firmware`)
              ...)  ...

从您的帖子中不清楚哪个是“其他”列。我假设firmware_id. 如果这是错误的，您需要相应地更改代码。

推荐阅读