Can't create table with Foreign Keys - errno:150??
Posted by:
Mike Post
Date: February 25, 2009 09:58PM
I'm trying to implement an inherited table into mySQL and i can't insert the 2nd FK (ignore the inheritance part, the priority is the FK problem).
Does anyone know what the problem is here:
CREATE TABLE Contacts (
`contact_ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`contact_type` ENUM('organisation', 'individual') NOT NULL,
`timestamp` DATETIME NOT NULL DEFAULT '2009-00-00 00:00:00',
`group` INT NOT NULL,
PRIMARY KEY (`contact_ID`, `contact_type`)
)ENGINE = INNODB;
CREATE TABLE Contact_Organisation (
org_ID INT(10) UNSIGNED NOT NULL,
c_type INT NOT NULL DEFAULT 1,
name VARCHAR(100) NULL ,
web VARCHAR(100) NULL ,
PRIMARY KEY (org_ID),
FOREIGN KEY (org_ID)
REFERENCES Contacts (contact_ID)
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY (c_type)
REFERENCES Contacts (contact_type)
ON DELETE CASCADE
ON UPDATE CASCADE
)ENGINE = INNODB;
...It works if i create the 2nd table without the 2nd FK:
CREATE TABLE Contact_Organisation (
org_ID INT(10) UNSIGNED NOT NULL,
c_type INT NOT NULL DEFAULT 1,
name VARCHAR(100) NULL ,
web VARCHAR(100) NULL ,
PRIMARY KEY (org_ID),
FOREIGN KEY (org_ID)
REFERENCES Contacts (contact_ID)
ON DELETE CASCADE
ON UPDATE CASCADE
)ENGINE = INNODB;
I've tried making the 1st table's engine MYISAM, and i've tried removing the default value from the 2nd table attribute 'c_type'. I've also tried making 'c_type' a PK. Nothing has worked, i just the error of:
"Can't create table 'Contact_Organisation.frm' (errno: 150)"
...What is the problem?