mysql - 创建外键时出错(检查数据类型)

Question

我正在尝试在两个表之间建立关系。这是每个表的查询和外键创建，

CREATE TABLE IF NOT EXISTS `quotes` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `quote` text COLLATE utf8_unicode_ci NOT NULL,
  `author` int(11) NOT NULL,
  `topic` int(11) NOT NULL,
  `language` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  KEY `author` (`author`),
  KEY `topic` (`topic`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE TABLE IF NOT EXISTS `authors` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `author` int(11) NOT NULL,
  `period` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `photo` text COLLATE utf8_unicode_ci NOT NULL,
  `references` text COLLATE utf8_unicode_ci NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  KEY `author` (`author`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

ALTER TABLE `quotes` ADD FOREIGN KEY ( `author` ) REFERENCES `mytestdb`.`authors` (
`id`
) ON DELETE RESTRICT ON UPDATE RESTRICT ;

但它抛出以下错误，

Error creating foreign key on author (check data types)

我不确定这个错误是关于什么的。对此的任何帮助将不胜感激。

Answer 1

数据类型和符号必须相同

int 与 unsigned int 不同

显示宽度无关

---

author 是一个 signed int

id 是一个无符号整数

来自manual page :

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)。它们不是同一个符号。

10和11的显示宽度无关。

演示

create table referenced
(   id int(11) primary key
);

create table referencing
(   id int primary key,
    author int(10) not null,
    CONSTRAINT fk_blah FOREIGN KEY (author) REFERENCES referenced(id)
);

没问题

CREATE TABLE IF NOT EXISTS `quotes` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `quote` text COLLATE utf8_unicode_ci NOT NULL,
  `author` int(11) NOT NULL,
  `topic` int(11) NOT NULL,
  `language` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  KEY `author` (`author`),
  KEY `topic` (`topic`)
) ENGINE=InnoDB;


CREATE TABLE IF NOT EXISTS `authors` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `author` int(11) NOT NULL,
  `period` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `photo` text COLLATE utf8_unicode_ci NOT NULL,
  `references` text COLLATE utf8_unicode_ci NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  KEY `author` (`author`),
  CONSTRAINT fk_blah222 FOREIGN KEY (author) REFERENCES quotes(id)
) ENGINE=InnoDB;

没问题