我需要向表中添加复合外键,该表的 struct 如下所示
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`);
有位置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;
这给了我一个错误:General error: 1215 Cannot add foreign key constraint
有人知道问题出在哪里吗.