[SOLVED] How to create composite foreign key in Mysql

Issue

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.

Solution

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.

Answered By – Bill Karwin

Answer Checked By – Clifford M. (BugsFixing Volunteer)