Q:

Error creating foreign key (check data types)

I'm trying to create a relation between two tables. And here is the query for each table and the foreign key creation,
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 ;

But it is throwing the following error,
Error creating foreign key on author (check data types)

I'm not sure what this error about. Any help on this would be greatly appreciated.

A:

The datatypes and signs must be the same
int is not the same as unsigned int
the display width is irrelevant

author is a signed int
id is an unsigned int
From the 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.

They are of the same size (int). They are not of the same sign.
The display width of 10 and 11 is irrelevant.
Demo
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)
);

works no problem
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;

works no problem

