mysql - 错误1822（HY000）：无法添加外键约束。 缺少约束索引(ERROR 1822 (HY000): Failed to add the foreign key constraint. Missing index for constraint)

Question

I've been working on this sql project about a game rental store.

(我一直在从事有关游戏租赁商店的sql项目。)

I've created the following tables

(我创建了以下表格)

CREATE TABLE CUSTOMER(
MEMBERSHIP_ID NUMERIC(10) PRIMARY KEY,
FNAME VARCHAR(10) NOT NULL,
LNAME VARCHAR(10) NOT NULL,
ADDRESS VARCHAR(100) NOT NULL,
TELEFONE NUMERIC(10) NOT NULL,
EMAIL VARCHAR(50) NOT NULL,
BIRTHDAY DATE);

AND:

(和：)

CREATE TABLE INVENTORY(
-> GAME_ID VARCHAR(10) PRIMARY KEY,
-> TITLE VARCHAR(20) NOT NULL,
-> PLATAFORM VARCHAR(10) NOT NULL,
-> EDITION YEAR,
-> GENRE VARCHAR(10) NOT NULL,
-> PUBLISHER VARCHAR(10) NOT NULL,
-> PRICE NUMERIC(4,2) DEFAULT 00.00 NOT NULL);

AND:

(和：)

CREATE TABLE RENTAL (
-> RENTAL_NO VARCHAR(10) PRIMARY KEY,
-> DATE_OUT DATE NOT NULL,
-> DATE_OF_RETURN DATE NOT NULL,
-> MEMBERSHIP_ID NUMERIC(10),
-> GAME_ID VARCHAR(10),
-> INDEX (MEMBERSHIP_ID),
-> CONSTRAINT FK_RENTAL_CUST FOREIGN KEY(MEMBERSHIP_ID) REFERENCES
-> CUSTOMER (MEMBERSHIP_ID),
-> INDEX (GAME_ID),
-> CONSTRAINT FK_RENTAL_INV FOREIGN KEY(GAME_ID) REFERENCES
-> INVENTORY (GAME_ID));

Now I need a table for the transaction that will connect the price and I've been getting the error mentioned:

(现在，我需要一个用于交易的表，该表将连接价格，并且我一直在得到提到的错误：)

CREATE TABLE OPEN_TRANSACTION (
RENTAL_NO VARCHAR (10),
MEMBERSHIP_ID NUMERIC (10),
DATE_OUT DATE NOT NULL,
DATE_OF_RETURN DATE NOT NULL,
GAME_ID VARCHAR (10),
ITEM_PRICE NUMERIC(4,2) DEFAULT 00.00 NOT NULL,
INDEX (RENTAL_NO),
INDEX (MEMBERSHIP_ID),
INDEX (GAME_ID),
INDEX (ITEM_PRICE),
INDEX (DATE_OUT),
INDEX (DATE_OF_RETURN),
CONSTRAINT PK_OPEN_TRANSACTION PRIMARY KEY (RENTAL_NO, GAME_ID, DATE_OUT),
CONSTRAINT FK_OPENTRAN_RENTAL FOREIGN KEY (RENTAL_NO, DATE_OUT, DATE_OF_RETURN) REFERENCES
RENTAL (RENTAL_NO, DATE_OUT, DATE_OF_RETURN),
CONSTRAINT FK_OPENTRAN_CUST FOREIGN KEY (MEMBERSHIP_ID) REFERENCES
CUSTOMER (MEMBERSHIP_ID),
CONSTRAINT FK_OPENTRAN_INV FOREIGN KEY (GAME_ID) REFERENCES
INVENTORY (GAME_ID),
CONSTRAINT FK_OPENTRAN_INV FOREIGN KEY (ITEM_PRICE) REFERENCES
INVENTORY (PRICE));

  ask by Caroline Mártire translate from so

Answer 1

等待大神答复