mysql - MySQL 中的 "Cannot add foreign key constraint"错误

Question

我不断收到此错误，但我不确定我做错了什么。我

错误的 SQL 查询:

CREATE TABLE members
(
  member_ID INT NOT NULL,
  email VARCHAR(255) NOT NULL,
  phone CHAR(10) NOT NULL,
  rating VARCHAR(5) NOT NULL,
  hashed_password VARCHAR(255) NOT NULL,
  member_level INT NOT NULL,
  PRIMARY KEY (member_ID),
  FOREIGN KEY (member_level) REFERENCES member_level(member_level)
)

MySQL 说:

#1215 - Cannot add foreign key constraint

我尝试运行的 SQL 如下，我不知道它的问题是什么

CREATE TABLE category
(
  category_ID INT NOT NULL,
  category_name VARCHAR(255) NOT NULL,
  PRIMARY KEY (category_ID)
);
CREATE TABLE member_level
(
  member_level CHAR(1) NOT NULL,
  member_level_description VARCHAR(255) NOT NULL,
  PRIMARY KEY (member_level)
);
CREATE TABLE members
(
  member_ID INT NOT NULL,
  email VARCHAR(255) NOT NULL,
  phone CHAR(10) NOT NULL,
  rating VARCHAR(5) NOT NULL,
  hashed_password VARCHAR(255) NOT NULL,
  member_level INT NOT NULL,
  PRIMARY KEY (member_ID),
  FOREIGN KEY (member_level) REFERENCES member_level(member_level)
);
CREATE TABLE tools
(
  tool_ID INT NOT NULL,
  serial_number VARCHAR(255) NOT NULL,
  tool_name VARCHAR(255) NOT NULL,
  tool_description VARCHAR(255) NOT NULL,
  tool_picture VARCHAR(255) NOT NULL,
  Member_ID INT NOT NULL,
  PRIMARY KEY (tool_ID),
  FOREIGN KEY (member_ID) REFERENCES members(member_ID)
);
CREATE TABLE tool_category
(
  tool_ID INT NOT NULL,
  category_ID INT NOT NULL,
  FOREIGN KEY (tool_ID) REFERENCES tools(tool_ID),
  FOREIGN KEY (category_ID) REFERENCES category(category_ID)
);
-- Populate tables
INSERT INTO member_level VALUES ('a', 'admin');
INSERT INTO member_level VALUES ('m', 'member');

Answer 1

表 members 中的

member_level 的类型为 int，而表 member_level 的类型为 CHAR(1).

它们必须是同一类型。