Foreign Keys

[[Coyot3] 0]

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.

[justsomeguy 1,135]

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.

[Reg Edit 0]

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.

[[Coyot3] 0]

@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

[Reg Edit 0]

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

[[Coyot3] 0]

@Reg Edit: You're right !Thanks a bunch