mysql - InnoDB CREATE TABLE 抛出错误 150

Question

此创建表抛出错误 150:

CREATE TABLE IF NOT EXISTS published(
isbn VARCHAR(13) NOT NULL,
publisherid INTEGER NOT NULL,
year INTEGER NOT NULL,
lastupdate TIMESTAMP NOT NULL,
lastupdateby INTEGER NOT NULL,
FOREIGN KEY (lastupdateby) REFERENCES librarians (librarianid),
FOREIGN KEY (isbn) REFERENCES books,
FOREIGN KEY (publisherid) REFERENCES publishers,
PRIMARY KEY (isbn, publisherid)
) ENGINE = INNODB;

我已经检查了它引用的表(librarians、books 和 publishers)在 SQL 达到这个位时已经创建.这是他们的 DDL:

CREATE TABLE IF NOT EXISTS librarians(
librarianid INTEGER AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
password VARCHAR(255) NOT NULL,
canread BOOLEAN NOT NULL,
canwrite BOOLEAN NOT NULL,
canexec BOOLEAN NOT NULL,
lastupdate TIMESTAMP NOT NULL,
lastupdateby INTEGER NOT NULL,
FOREIGN KEY (lastupdateby) REFERENCES librarians (librarianid)
) ENGINE = INNODB;

CREATE TABLE IF NOT EXISTS books(
isbn VARCHAR(13) PRIMARY KEY,
title VARCHAR(255) NOT NULL,
lastupdate TIMESTAMP NOT NULL,
lastupdateby INTEGER NOT NULL,
FOREIGN KEY (lastupdateby) REFERENCES librarians (librarianid)
) ENGINE = INNODB;

CREATE TABLE IF NOT EXISTS publishers(
publisherid INTEGER PRIMARY KEY AUTO_INCREMENT,
publishername VARCHAR(255) UNIQUE NOT NULL,
lastupdate TIMESTAMP NOT NULL,
lastupdateby INTEGER NOT NULL,
FOREIGN KEY (lastupdateby) REFERENCES librarians (librarianid)
) ENGINE = INNODB;

此外，我还检查了我发现的以下指南 here :

引用的表也应该是 InnoDB。好吧，我什至明确指定了这一点。

引用表必须有索引和主键。外键字段是它们各自引用表的(唯一)主键。除非我误解了这个要求，否则应该满足它。

FK 列和引用的PK 列的SQL 数据类型必须相同。 除非我没检查错，否则这个应该没问题。

• lastupdateby INTEGER NOT NULL --> librarianid INTEGER AUTO_INCREMENT PRIMARY KEY
• isbn VARCHAR(13) NOT NULL --> isbn VARCHAR(13) 主键
• publisherid INTEGER NOT NULL --> publisherid INTEGER PRIMARY KEY AUTO_INCREMENT

那么，我错过了什么？这个错误 150 的原因可能是什么？

Answer 1

您在表 books 和 publishers 的外键规范中缺少列引用。您需要如下更改这些行:

FOREIGN KEY (isbn) REFERENCES books (isbn),
FOREIGN KEY (publisherid) REFERENCES publishers(publisherid),