如何在Mysql中創建復合外鍵-有解無憂

我需要向結構看起來像的表添加復合外鍵

CREATE TABLE IF NOT EXISTS `discount_month_devices` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `discount_month_id` int(11) UNSIGNED NOT NULL,
  `global_device_id` int(11) DEFAULT NULL,
  `location_id` int(11) UNSIGNED DEFAULT NULL,
  `server_id` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `discount_month_id` (`discount_month_id`),
  KEY `global_device_id` (`global_device_id`),
  KEY `location_id` (`location_id`,`server_id`),
  KEY `server_id` (`server_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

設備表 DDL 看起來像

CREATE TABLE IF NOT EXISTS `devices` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `server_id` varchar(20) CHARACTER SET utf8mb4 NOT NULL,
  `device_id` int(11) DEFAULT NULL,
  `location_id` int(11) UNSIGNED DEFAULT NULL,
  `device_lat` float DEFAULT NULL,
  `device_long` float DEFAULT NULL,
   ....
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `devices_idx1` (`server_id`,`device_id`) USING BTREE,
  KEY `devices_idx5` (`server_id`) USING BTREE,
  KEY `devices_idx6` (`device_id`) USING BTREE,
  KEY `devices_idx8` (`server_id`,`owner_id`) USING BTREE,
  KEY `server_id` (`server_id`,`location_id`),
  KEY `devices_idx14` (`location_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1583586 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

ALTER TABLE `devices`
  ADD CONSTRAINT `devices_fk1` FOREIGN KEY (`server_id`,`location_id`) REFERENCES `locations` (`server_id`, `location_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `devices_fk2` FOREIGN KEY (`discount_month_id`) REFERENCES `discount_month` (`id`);

還有 location_id 復合索引。我可以分別為 location_id 和 server_id 創建 FK，因此列型別和范圍應該是正確的。

我想運行alter table，它應該添加看起來像

ALTER TABLE `discount_month_devices` ADD CONSTRAINT `discount_month_devices_fk3` 
FOREIGN KEY (`location_id`, `server_id`) REFERENCES `devices`(`location_id`, `server_id`) 
ON DELETE CASCADE ON UPDATE CASCADE;

這引發了一個錯誤：一般錯誤：1215 無法添加外鍵約束

有誰知道可能是什么問題。

uj5u.com熱心網友回復：

您必須按照列在參考表的鍵中出現的相同順序列出外鍵約束中的列。您的鍵devices已打開，(server_id, location_id)但您嘗試在外鍵約束中將它們參考為(location_id, server_id)。

嘗試這個：

ALTER TABLE `discount_month_devices` 
  ADD CONSTRAINT `discount_month_devices_fk3` 
  FOREIGN KEY (`server_id`, `location_id`) 
  REFERENCES `devices`(`server_id`, `location_id`)
  ON DELETE CASCADE ON UPDATE CASCADE;

鍵和約束中的列順序不需要與表定義中的列順序相匹配。

轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/445185.html

標籤：mysql sql 索引外键

上一篇：MySQLWhere子句與Union都得到錯誤的結果

下一篇：Mysql簡單查詢執行時間過長