MySQL Forums
Forum List  »  InnoDB

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?

Options: ReplyQuote


Subject
Views
Written By
Posted
Can't create table with Foreign Keys - errno:150??
4119
February 25, 2009 09:58PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.