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