insert first data into a recursive sql table

Question

I have the following table:

Create Table if not exists Categories(
    category_id int (10) primary key NOT NULL AUTO_INCREMENT,
    category_name varchar (20) NOT NULL,
    parent_category_id int (10) NOT NULL,   
    FOREIGN KEY (parent_category_id) REFERENCES Categories(category_id) ON DELETE CASCADE)

The table is holding every category I have on my site - and each category have a parent category (for example 'computer' is the parent category of 'programming') I have a few top categories which don't have any parent category => parent_category_id =0

My question is how to insert the data for the top categories. when i'm trying to do:

INSERT INTO  `databaseproject`.`categories` (`category_id` ,`category_name` ,`parent_category_id`)
VALUES (NULL ,  'computers',  '0')

I'm getting the error:

#1452 - Cannot add or update a child row: a foreign key constraint fails (`databaseproject`.`categories`, CONSTRAINT `categories_ibfk_1` 
FOREIGN KEY (`parent_category_id`) REFERENCES `categories` (`category_id`) ON DELETE CASCADE)

what can I do to insert those categories?

Denis de Bernardy · Accepted Answer · 2013-05-03 18:31:29Z

2

Make parent_category_id nullable, and parent categories have a null parent_category_id, or add a root row with id 0.

answered May 3, 2013 at 18:31

Denis de Bernardy

79.1k14 gold badges138 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

user330315 Over a year ago

I would advocate to use NULL instead of a "magic" number (and the root row would need to have the parent_category_id = NULL anyway)

Denis de Bernardy Over a year ago

@a_horse_with_no_name: Agreed on preferring NULL, but on occasion there's a bizarro software architect who insists on having no NULLs anywhere... I know at least one such person. :-) The root node can have (category_id, parent_category_id) = (0, 0).

haki Over a year ago

I can understand the architect - nulls effect usage of indexes (nulls are not indexes so indexes cannot be used). in DWH sytems - only magic numbers (as @a_horse_with_no_name calls them).

Denis de Bernardy Over a year ago

@haki: horse and I are Postgres junkies. In our ghetto, nulls are indexed just fine in btree indexes. Else yeah... my father's ghetto is SQL-Server, where I seem to remember that nulls are not indexed indeed at some point (and maybe still aren't).

user330315 Over a year ago

@Denis: I wasn't actually thinking about indexing at all, only about properly representing this kind of data.

|

Whit Kemmey · Accepted Answer · 2013-05-03 18:50:32Z

You have two problems. First, category_id is an auto_increment field, meaning the database will create a value for you when you insert a new row, so you don't need to provide it. You certainly can't set it to null, since you have specifically said it can't be null, which is absolutely what you want for an auto-incrementing id field. Just change your insert to:

INSERT INTO  'databaseproject'.'categories' ('category_name' ,'parent_category_id')
VALUES ('computers',  '0')

But you still have another problem. parent_category_id has to refer to a valid record in the categories table because of the constraint you created. You can solve this problem by allowing the field to be null (get rid of the "NOT NULL" when creating the parent_category_id), and using null instead of zero to indicate this is a top level record.

Collectives™ on Stack Overflow

insert first data into a recursive sql table

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related