MySQL Forums
Forum List  »  Newbie

Help! Foreign Key error from SQL generated by workbench modelling
Posted by: Kunlun Mao
Date: September 29, 2011 07:51PM

in my schema, only one table, and i use workbench modelling to design and generate the SQL source, and then forward it to mysql database, but innodb give me error, say "Error Code: 1005. Can't create table 'mydb.kiwi_locations' (errno: 150)".

This is my SQL code for this table and generated by workbench modelling. As you can see, there are three FKs which refer to the table itself, i want these 3 fields can only refer to datum in its own table.

CREATE TABLE IF NOT EXISTS `mydb`.`kiwi_locations` (
`lid` INT NOT NULL AUTO_INCREMENT ,
`display_name` VARCHAR(100) NULL ,
`description` TEXT NULL ,
`latitude` DOUBLE NULL ,
`longitude` DOUBLE NULL ,
`radius` DOUBLE NULL ,
`first_parent_loc_id` INT NULL ,
`second_parent_loc_id` INT NULL ,
`thrid_parent_loc_id` INT NULL ,
PRIMARY KEY (`lid`) ,
INDEX `lid` (`first_parent_loc_id` ASC, `second_parent_loc_id` ASC, `thrid_parent_loc_id` ASC) ,
CONSTRAINT `lid`
FOREIGN KEY (`first_parent_loc_id` , `second_parent_loc_id` , `thrid_parent_loc_id` )
REFERENCES `mydb`.`kiwi_locations` (`lid` , `lid` , `lid` )
ON DELETE RESTRICT
ON UPDATE CASCADE)
ENGINE = InnoDB

Options: ReplyQuote


Subject
Written By
Posted
Help! Foreign Key error from SQL generated by workbench modelling
September 29, 2011 07:51PM


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.