mySQL: Cannot add foreign key constraint when creation a table

Question

I want to create this table

CREATE TABLE `t_plan` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



CREATE TABLE `t_plan_x_currency` (
  `plan_id`             bigint(20)   NOT NULL ,
  `currency_symbol`     varchar(20) NOT NULL DEFAULT 'EUR',
  `price` decimal(12,6) NOT NULL DEFAULT '0',
  PRIMARY KEY (`plan_id`,`currency_symbol`),
  CONSTRAINT `t_plan_x_currency_ibfk_1` FOREIGN KEY (`plan_id`) REFERENCES `t_plan` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

but I got this error:

Cannot add foreign key constraint

The datatype of both the columns must match for creating foreign key relationship. — Aura, Jan 25 '19 at 19:50
You might like this checklist for foreign keys, which I contributed to: https://stackoverflow.com/a/4673775/20860 — Bill Karwin, Jan 25 '19 at 19:51

score 2 · Accepted Answer · answered Jan 25 '19 at 19:49

The columns involved in a foreign key relationship have to be of the same type. You have different types, int(11) for t_plan.id and bigint(20) for t_plan_x_currency.plan_id.

Make t_plan.id also a bigint(20).

CREATE TABLE `t_plan` (
  `id` bigint(20) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `t_plan_x_currency` (
  `plan_id`             bigint(20)   NOT NULL ,
  `currency_symbol`     varchar(20) NOT NULL DEFAULT 'EUR',
  `price` decimal(12,6) NOT NULL DEFAULT '0',
  PRIMARY KEY (`plan_id`,`currency_symbol`),
  CONSTRAINT `t_plan_x_currency_ibfk_1` FOREIGN KEY (`plan_id`) REFERENCES `t_plan` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Or make t_plan_x_currency.plan_id an int(11), what ever is more desirable for you.

mySQL: Cannot add foreign key constraint when creation a table

1 Answers1