foreign key constraint fails
Hi folks
I'm not totally new to MySQL, but learning every day, like with this problem uploading records to a table from a web form with links to a lookup table for one of the field values.
The lookup table has ~200 records and is indexed on the record ID, primary key. The upload works if the Lookup table ID in the SQL is less than 115, but not when ≥115 with the following error: "1452... foreign key constraint fails". I've tried enlarging the filed size, using MyISAM engine (though I default all my tables to InnoDB), no go. Table structures and SQL below, lookup table first. (Lookup table DOES contain a unique record with ID 144):
Thanks for any suggestions
SDG
jon
CREATE TABLE Lookup (
LuID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
LuCat VARCHAR(50),
LuVal VARCHAR(50),
INDEX (LuID)
)ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE CmeList (
CeID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
CeDateTime DATETIME,
CeEvCat INT UNSIGNED NOT NULL,
CeHours DECIMAL(3,2) UNSIGNED NOT NULL,
CeDescript TEXT,
CeDoc BLOB,
INDEX (CeEvCat, CeID),
FOREIGN KEY (CeEvCat) REFERENCES Lookup(LuID)
) ENGINE=InnoDB;
INSERT INTO CmeList (CeDateTime, CeEvCat, CeHours, CeDescript) VALUES ("12/01/2012",144,2.5,"This is a test")