MySQL Forums :: Newbie :: Help! Foreign Key error from SQL generated by workbench modelling


Advanced Search

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 Kunlun Mao 09/29/2011 07:51PM
Re: Help! Foreign Key error from SQL generated by workbench modelling Rick James 10/01/2011 11:03AM


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.