Jump to content
[Coyot3]

Foreign Keys

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

@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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...