MySQL 错误 1005

Question

对，所以代码是这样的:

CREATE TABLE Seller (
sID INT AUTO_INCREMENT
    PRIMARY KEY,
sName CHAR (10), 
sAddress CHAR (30)
)ENGINE = INNODB;
CREATE TABLE Sale (
sID INT AUTO_INCREMENT,
iName CHAR(10),
CONSTRAINT sa_pk
    PRIMARY KEY (sID, iName),
CONSTRAINT sa_fk1
    FOREIGN KEY (sID)
    REFERENCES Seller (sID)
    ON UPDATE CASCADE,
CONSTRAINT sa_fk2
    FOREIGN KEY (iName)
    REFERENCES Item (iName)
    ON UPDATE CASCADE
)ENGINE = INNODB;
CREATE TABLE Item (
iPrice REAL,
iStock INT,
iName CHAR(10)
    PRIMARY KEY,
iDescription CHAR (30)
)ENGINE = INNODB;
CREATE TABLE Copy (
oQuantity INT,
iName CHAR(10),
CONSTRAINT co_pk
    PRIMARY KEY (oQuantity, iName),
CONSTRAINT co_fk1
    FOREIGN KEY (oQuantity)
    REFERENCES Orders (oQuantity)
    ON UPDATE CASCADE,
CONSTRAINT co_fk2
    FOREIGN KEY (iName)
    REFERENCES Item (iName)
    ON UPDATE CASCADE
)ENGINE = INNODB;
CREATE TABLE Orders (
oSeller CHAR (10),
oTypes CHAR (10),
oQuantity INT,
oPrice REAL,
oID INT AUTO_INCREMENT
    PRIMARY KEY
)ENGINE = INNODB;
CREATE TABLE Customer (
cID INT AUTO_INCREMENT
    PRIMARY KEY,
cName CHAR (10),
cAddress CHAR (30)
)ENGINE = INNODB;

我在开始时有删除命令，所以每次我运行这个脚本时，它们都会被删除 oooor 至少除了复制和销售之外的所有命令。每当我尝试删除它们时，它都会失败，因为它们从未创建过，我不断收到错误 1005 (HY000) 废话，然后无法创建表 x。请帮我解决这个烦人的问题。 tar !

Answer 1

在您的sale 表中，您有

CONSTRAINT sa_fk2 FOREIGN KEY (iName) REFERENCES Item (iName)
    ON UPDATE CASCADE

同时，您随后创建了 Item 表。

---

要创建 Copy 表，您需要在 Order 表中的 oQuantity 字段上建立索引。

INDEX `oQty` (`oQuantity`)

将以上内容添加到 Order 表中可以创建表。

这是 sqlfiddle .