[Coyot3] 0 Posted February 20, 2008 Report Share Posted February 20, 2008 Hi all,think about the case you want to describe a table like: CREATE TABLE categories( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(50) NOT NULL, description VARCHAR(255) NULL, mother INT NOT NULL DEFAULT 0, PRIMARY KEY (id), FOREIGN KEY (mother) REFERENCES categories(id)) ENGINE=INNODB; the goal is to get support to a leveled menu likecars BMW FORDmotos KAWASAKIThe problem I got, when trying to insert the first (and least) record, was #1452 - Cannot add or update a child row: a foreign key constraint fails (`bookmarks/categories`, CONSTRAINT `categories_ibfk_1` FOREIGN KEY (`mother`) REFERENCES `categories` (`id`)) just because I have a circular (or auto reference) foreign key: categories.mother REFERENCES categories.id!When I thought the problem it seemed the best solution to solve the problem.I can take out the foreign key and that will become a regular attribute but I will lose the referential integrity.I'm sure it's a problem with a simple solution but I'm not being able to get it. Any idea!?Thanks in advance. Quote Link to post Share on other sites
justsomeguy 1,135 Posted February 20, 2008 Report Share Posted February 20, 2008 You can't have a circular key like that on one table. When you insert the first row there is nothing for the foreign key to point to. Either remove the foreign key constraint and sacrifice referential integrity, or create a second table with 2 columns for parent/child relationships that will both reference the id from the other table. Then you can make sure you have records in the categories table to reference before you enter records into the other table. Quote Link to post Share on other sites
Reg Edit 0 Posted February 20, 2008 Report Share Posted February 20, 2008 I can take out the foreign key and that will become a regular attribute but I will lose the referential integrity.I'm sure it's a problem with a simple solution but I'm not being able to get it. Any idea!?Thanks in advance.Allow nulls in the mother column. That will allow you to load the data, and you can set the mother values afterwards. Quote Link to post Share on other sites
[Coyot3] 0 Posted February 21, 2008 Author Report Share Posted February 21, 2008 @justsomeguy: thanks for your answer.Your suggestion is to create a schema like categories(id,name,description)catCat(parent,id) where parent will be foreign key from categories (like id) and both will form the primary key of catCat.You suggestion make sense if we think about transitive closure: if A is B's mother and B is C's mother then A is C's mother.Which solution you think is better?@Reg Edit: Allow nulls in mother column is only possible if the attribute is not FOREIGN KEY (neither PRIMARY).I'm not sure that this is the best solution. I'm thinking to create other table to store relations. What you think about it?Thanks all of you Quote Link to post Share on other sites
Reg Edit 0 Posted February 21, 2008 Report Share Posted February 21, 2008 Allow nulls in mother column is only possible if the attribute is not FOREIGN KEY (neither PRIMARY).Not so! A foreign key can be optional by allowing nulls. This preserves referential integrity because the foreign key must be set to an existing id or null. Try this:CREATE TABLE categories( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(50) NOT NULL, description VARCHAR(255) NULL, mother INT NULL, PRIMARY KEY (id), FOREIGN KEY (mother) REFERENCES categories(id)) ENGINE=INNODB You can then insert records: insert into categories (name, description) values ('n1', 'd1')insert into categories (name, description) values ('n2', 'd2') And you can then set mother. In the case where you have inserted records with ids 1 and 2: update categories set mother = 1 where id = 2 Quote Link to post Share on other sites
[Coyot3] 0 Posted February 21, 2008 Author Report Share Posted February 21, 2008 @Reg Edit: You're right !Thanks a bunch Quote Link to post Share on other sites
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.