mysql - 无法使用 FOREIGN KEY 在 MySQL 中添加外键约束

Question

我刚刚创建了这个表:

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;

然后我想创建这个:

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

但这是不可能的，因为我得到了这个错误:

Cannot add foreign key constraint

Answer 1

FK 列必须与引用表中的 PK 具有相同的类型:

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.

你有: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

因此您可以将:t_application.id 更改为 int(11) unsigned

或t_device。application_id到int(11)