MySQL Error 1005

Question

Right so code goes as such:

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;

I've got drop commands at the beginning so every time i run this script they're removed oooor at least all of them except copy and sale. Whenever i try to drop them it fails as they're never created i keep getting the ERROR 1005 (HY000) crap followed by can't create table x. Please help me sort this annoying pest of a problem. Tar!

Answer 1

In your sale table, you have

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

While, you are creating the Item table afterwards.

---

For creating the Copy table, you need an index on the oQuantity field in the Order table.

INDEX `oQty` (`oQuantity`)

Adding the above to Order table gets you to create the tables.

Here's the sqlfiddle.