mysql - ERROR 1822 (HY000): 添加外键约束失败。缺少约束索引

Question

我一直在研究这个关于游戏租赁商店的 sql 项目。

我创建了以下表格

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);

和：

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);

和：

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));

现在我需要一个用于连接价格的交易表，并且我收到了提到的错误：

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));

标签： mysqlforeign-keysconstraints

Answer 1

错误消息的原因是，您有引用另一个表中的列元组的外键约束，其中该元组中的列没有（唯一）索引（以相同的顺序）。即这是约束FK_OPENTRAN_RENTAL和FK_OPENTRAN_INV。

但是您似乎误解了外键的用途。外键引用其他表中的行，并且不检查任何奇数列值是否在两个表中相同。实际上，您应该将值保留在原始表中，您可以通过使用外键从另一个表中检索它来识别另一个表中的正确行并从那里读取信息。将同一条信息存储在多个地方甚至是危险的，因为不同地方的值可能会有所不同，并且您不再知道哪个是正确的。

所以列DATE_OUT和DATE_OF_RETURN不属于OPEN_TRANSACTION. 只需RENTAL_NO进入OPEN_TRANSACTION. 有了它，您可以在其中找到正确的行RENTAL并从中获取值。这同样适用于ITEM_PRICEin OPEN_TRANSACTION。你有GAME_ID外键，可以找到正确的游戏，从而找到正确的价格INVENTORY。

相应地更改您的CREATE陈述。删除多余的列也会以不再有错误的方式删除或更改外键约束。

CREATE TABLE OPEN_TRANSACTION (
RENTAL_NO VARCHAR (10),
MEMBERSHIP_ID NUMERIC (10),
GAME_ID VARCHAR (10),
INDEX (RENTAL_NO),
INDEX (MEMBERSHIP_ID),
INDEX (GAME_ID),
CONSTRAINT PK_OPEN_TRANSACTION PRIMARY KEY (RENTAL_NO, MEMBERSHIP_ID, GAME_ID),
CONSTRAINT FK_OPENTRAN_RENTAL FOREIGN KEY (RENTAL_NO) REFERENCES
RENTAL (RENTAL_NO),
CONSTRAINT FK_OPENTRAN_CUST FOREIGN KEY (MEMBERSHIP_ID) REFERENCES
CUSTOMER (MEMBERSHIP_ID),
CONSTRAINT FK_OPENTRAN_INV FOREIGN KEY (GAME_ID) REFERENCES
INVENTORY (GAME_ID));