[Coyot3] Posted February 20, 2008 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. Link to comment Share on other sites More sharing options...
justsomeguy Posted February 20, 2008 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. Link to comment Share on other sites More sharing options...
Reg Edit Posted February 20, 2008 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. Link to comment Share on other sites More sharing options...
[Coyot3] Posted February 21, 2008 Author 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 Link to comment Share on other sites More sharing options...
Reg Edit Posted February 21, 2008 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 Link to comment Share on other sites More sharing options...
[Coyot3] Posted February 21, 2008 Author Share Posted February 21, 2008 @Reg Edit: You're right !Thanks a bunch Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.