Home > Back-end >  How create composite foreign key in Mysql
How create composite foreign key in Mysql

Time:03-17

I need to add composite foreign key to table which structure looks like

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;

Devices table DDL looks like

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`);

There are location_id composite index. I can create FK for location_id and server_id separately so columns types and ranges should be right.

I would like to run alter table which should add the foreign which looks like

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;

This throws me an error: General error: 1215 Cannot add foreign key constraint

Does anybody know what could be the problem.

CodePudding user response:

You must list the columns in the foreign key constraint in the same order that they appear in a key in the referenced table. Your key in devices is on (server_id, location_id) but you tried to reference them in your foreign key constraint as (location_id, server_id).

Try this:

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;

The order of columns in keys and constraints is not required to match the order of columns in the table definition.

  • Related