mysql - 以下 SQL 在 MySQL 中生成错误 1005 和 errno 150

Question

以下 SQL 导致 mysql 生成错误 1005，消息中出现 errno 150:

CREATE TABLE Review_Record (
  id int NOT NULL PRIMARY KEY AUTO_INCREMENT UNIQUE ,
  file MEDIUMBLOB,
  submission_id INT not null ,
  reviewer_id VARCHAR(50) not null ,
  file_mime VARCHAR(20),
  rating INT,
  assigned_time TIMESTAMP default CURRENT_TIMESTAMP,
  completed_time TIMESTAMP,
  completed BOOLEAN DEFAULT FALSE ,
  FOREIGN KEY (submission_id) REFERENCES Submission (id),
  FOREIGN KEY (reviewer_id) REFERENCES Reviewer (id)
) ENGINE = INNODB;

查找mysql文档后，我找到了关于此错误的以下描述:

• 1005 (ER_CANT_CREATE_TABLE)

Cannot create table. If the error message refers to error 150, table creation failed because a foreign key constraint was not correctly formed. If the error message refers to error −1, table creation probably failed because the table includes a column name that matched the name of an internal InnoDB table.

但是我的外键约束是如何错误形成的？

Answer 1

创建外键时，确保数据类型和字符集相同非常重要。

在本例中，Reviewer.id 的数据类型 (int) 与 Review_Record.reviewer_id (varchar 50) 不匹配。

(引用评论)