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
– Aura, Commented Jan 25, 2019 at 19:50
You might like this checklist for foreign keys, which I contributed to: stackoverflow.com/a/4673775/20860 — Bill Karwin
– Bill Karwin, Commented Jan 25, 2019 at 19:51

sticky bit · Accepted Answer · 2019-01-25 19:49:25Z

2

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.

answered Jan 25, 2019 at 19:49

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mySQL: Cannot add foreign key constraint when creation a table

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related