CODEWITHSUNDEEP
mysqlsql
Nuñito Calzada
Nuñito Calzada

Reputation: 2056

Cannot add foreign key constraint in MySQL with FOREIGN KEY

I just created this table:

CREATE TABLE `t_application` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `application_desc` varchar(255) DEFAULT NULL,
  `application_key` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK_cotl49evfo7w4plf6213uaruc` (`application_key`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Then I want to create this one:

CREATE TABLE `t_device` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `device_key` varchar(50) DEFAULT NULL,
  `device_type` varchar(50) DEFAULT NULL,
  `application_id` int(11) unsigned NOT NULL,
  `device_desc` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `application_id` (`application_id`),
  CONSTRAINT `t_device_app` FOREIGN KEY (`application_id`) REFERENCES `t_application` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

but its not possible because I got this error:

Cannot add foreign key constraint

Upvotes: 1

Views: 174

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175556

The FK column has to have the same type as PK in referenced table:

Using FOREIGN KEY Constraints

Corresponding columns in the foreign key and the referenced key must have similar data types. The size and sign of integer types must be the same. The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must be the same.

You have: int(11) <> int(11) unsigned

CREATE TABLE `t_application` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `application_desc` varchar(255) DEFAULT NULL,
  `application_key` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK_cotl49evfo7w4plf6213uaruc` (`application_key`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `t_device` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `device_key` varchar(50) DEFAULT NULL,
  `device_type` varchar(50) DEFAULT NULL,
  `application_id` int(11) unsigned NOT NULL,
  `device_desc` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `application_id` (`application_id`),
  CONSTRAINT `t_device_app` FOREIGN KEY (`application_id`) 
  REFERENCES `t_application` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

SqlFiddleDemo

So you can change: t_application.id to int(11) unsigned

or t_device.application_id to int(11)

Upvotes: 2

Related Questions