MySQL 无法添加外键约束 - 不匹配的字符集

Question

以下创建语句失败

DROP TABLE IF EXISTS `book` ;
DROP TABLE IF EXISTS `person` ;

CREATE TABLE `person` (
  `name` VARCHAR(30) NOT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `book` (
  `author` VARCHAR(30) NOT NULL,
  `title` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`author`, `title`),
  FOREIGN KEY (`author`)
    REFERENCES `person` (`name`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
) ENGINE = InnoDB ;

出现以下错误

ERROR 1215 (HY000): Cannot add foreign key constraint

并且所有的数据类型和字段名都是正确的。为什么外键约束失败？

Answer 1

我找到了答案，DEFAULT CHARSET 子句不匹配。第二个创建表语句还需要声明 DEFAULT CHARSET。

DROP TABLE IF EXISTS `book` ;
DROP TABLE IF EXISTS `person` ;

CREATE TABLE `person` (
  `name` VARCHAR(30) NOT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `book` (
  `author` VARCHAR(30) NOT NULL,
  `title` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`author`, `title`),
  FOREIGN KEY (`author`)
    REFERENCES `person` (`name`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
) ENGINE = InnoDB DEFAULT CHARSET=latin1;