I am not sure how to set up the foreign keys on this one, as I've never done
foreign keys before (and I'm sure it shows). I have a tree of information.
I can insert into the categories table, but not anything else - I get
foreign key errors (and I am not sure how to interpret the error).
Cannot add or update a child row: a foreign key constraint fails
(`db0/subcategories`, CONSTRAINT `subcategories_ibfk_2` FOREIGN KEY
(`subcat_id`) REFERENCES `subcategories` (`cat_id`) ON DELETE CASCADE)


I am also curious if the foreign key definition in subcategories would cause
the database to fail or error on a delete because of the recursion. (that's
where the tree is stored).


CREATE TABLE `categories` (
`cat_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`category` VARCHAR(100) NOT NULL DEFAULT '' COMMENT 'your category here',
PRIMARY KEY(`cat_id`),
UNIQUE KEY `ix_ccat_id` (`cat_id`),
INDEX `ix_ccat`(`category`)
) ENGINE=InnoDB;

CREATE TABLE `subcategories` (
`sub_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ignore',
`cat_id` INTEGER UNSIGNED NOT NULL DEFAULT 0 COMMENT 'copy of cat_id from
categories table',
`subcat_id` INTEGER UNSIGNED NOT NULL DEFAULT 0 COMMENT 'cat_id of
subcategory',
FOREIGN KEY (`cat_id`) REFERENCES `categories`(`cat_id`) ON DELETE
CASCADE,
FOREIGN KEY (`subcat_id`) REFERENCES `subcategories`(`cat_id`) ON DELETE
CASCADE,
PRIMARY KEY(`sub_id`),
INDEX `ix_sccat_id`(`cat_id`),
INDEX `ix_scsubcat_id`(`subcat_id`)
) ENGINE=InnoDB;

CREATE TABLE `categoryroots` (
`root_id` int(10) unsigned NOT NULL auto_increment COMMENT 'ignore',
`cat_id` int(10) unsigned NOT NULL default '0' COMMENT 'copy of cat_id
from categories table',
PRIMARY KEY (`root_id`),
FOREIGN KEY (`cat_id`) REFERENCES `subcategories`(`cat_id`) ON DELETE
CASCADE,
UNIQUE KEY `ix_crcat_id` (`cat_id`)
) ENGINE=InnoDB;

/*
c
f-b
g |
h |
i-c-a
j |
k |
l-d
m
----n
*/
INSERT INTO categories(cat_id,category) VALUES
(1,'a'),
(2,'b'),
(3,'c'),
(4,'d'),
(5,'e'),
(6,'f'),
(7,'g'),
(8,'h'),
(9,'i'),
(10,'j'),
(11,'k'),
(12,'l'),
(13,'m'),
(14,'n');

INSERT INTO categoryroots(cat_id) VALUES
(1),
(14);

INSERT INTO subcategories(cat_id,subcat_id) VALUES
(1,2),
(1,3),
(1,4),
(2,5),
(2,6),
(2,7),
(3,8),
(3,9),
(3,10),
(4,11),
(4,12),
(4,13);

Jim Michaels wrote:
> I am not sure how to set up the foreign keys on this one, as I've never done
> foreign keys before (and I'm sure it shows). I have a tree of information.
> I can insert into the categories table, but not anything else - I get
> foreign key errors (and I am not sure how to interpret the error).
> Cannot add or update a child row: a foreign key constraint fails
> (`db0/subcategories`, CONSTRAINT `subcategories_ibfk_2` FOREIGN KEY
> (`subcat_id`) REFERENCES `subcategories` (`cat_id`) ON DELETE CASCADE)
>
>
> I am also curious if the foreign key definition in subcategories would cause
> the database to fail or error on a delete because of the recursion. (that's
> where the tree is stored).
>
>
> CREATE TABLE `categories` (
> `cat_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
> `category` VARCHAR(100) NOT NULL DEFAULT '' COMMENT 'your category here',
> PRIMARY KEY(`cat_id`),
> UNIQUE KEY `ix_ccat_id` (`cat_id`),
> INDEX `ix_ccat`(`category`)
> ) ENGINE=InnoDB;
>
> CREATE TABLE `subcategories` (
> `sub_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ignore',
> `cat_id` INTEGER UNSIGNED NOT NULL DEFAULT 0 COMMENT 'copy of cat_id from
> categories table',
> `subcat_id` INTEGER UNSIGNED NOT NULL DEFAULT 0 COMMENT 'cat_id of
> subcategory',
> FOREIGN KEY (`cat_id`) REFERENCES `categories`(`cat_id`) ON DELETE
> CASCADE,
> FOREIGN KEY (`subcat_id`) REFERENCES `subcategories`(`cat_id`) ON DELETE
> CASCADE,
> PRIMARY KEY(`sub_id`),
> INDEX `ix_sccat_id`(`cat_id`),
> INDEX `ix_scsubcat_id`(`subcat_id`)
> ) ENGINE=InnoDB;
>
> CREATE TABLE `categoryroots` (
> `root_id` int(10) unsigned NOT NULL auto_increment COMMENT 'ignore',
> `cat_id` int(10) unsigned NOT NULL default '0' COMMENT 'copy of cat_id
> from categories table',
> PRIMARY KEY (`root_id`),
> FOREIGN KEY (`cat_id`) REFERENCES `subcategories`(`cat_id`) ON DELETE
> CASCADE,
> UNIQUE KEY `ix_crcat_id` (`cat_id`)
> ) ENGINE=InnoDB;
>

(data snipped)

Jim,

Your problem is right here:

FOREIGN KEY (`subcat_id`) REFERENCES `subcategories`(`cat_id`) ON
DELETE CASCADE,

Generally you don't use a foreign key to reference the same table. If I
understand what you're trying to do, referencing categories should be
sufficient.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

"Jim Michaels" <jmichae3@yahoo.com> wrote in message
news:5rOdnSBK5PrqbkrenZ2dnUVZ_sydnZ2d@comcast.com. ..
> Cannot add or update a child row: a foreign key constraint fails
> (`db0/subcategories`, CONSTRAINT `subcategories_ibfk_2` FOREIGN KEY
> (`subcat_id`) REFERENCES `subcategories` (`cat_id`) ON DELETE CASCADE)

You can't add to a table a reference to the same table if the table hasn't
been created yet.
This is true if the SQL interpreter is just parsing the CREATE TABLE
statement.

To make self-referencing foreign keys, you have to create the table without
that foreign key constraint (but do create the field to be used as the
reference), then use ALTER TABLE to add the constraint.

For example:

CREATE TABLE `subcategories` (
`sub_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ignore',
`cat_id` INTEGER UNSIGNED NOT NULL DEFAULT 0 COMMENT 'copy of cat_id from
categories table',
`subcat_id` INTEGER UNSIGNED NOT NULL DEFAULT 0 COMMENT 'cat_id of
subcategory',
FOREIGN KEY (`cat_id`) REFERENCES `categories`(`cat_id`) ON DELETE
CASCADE,
PRIMARY KEY(`sub_id`),
INDEX `ix_sccat_id`(`cat_id`),
INDEX `ix_scsubcat_id`(`subcat_id`)
) ENGINE=InnoDB;

ALTER TABLE `subcategories` ADD CONSTRAINT
FOREIGN KEY (`subcat_id`) REFERENCES `subcategories`(`cat_id`)
ON DELETE CASCADE;

Regards,
Bill K.

Jerry Stuckle <jstucklex@attglobal.net> wrote:
> Jim Michaels wrote:
>>
>> CREATE TABLE `subcategories` (
>> `sub_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
>> `cat_id` INTEGER UNSIGNED NOT NULL DEFAULT 0,
>> `subcat_id` INTEGER UNSIGNED NOT NULL DEFAULT 0,
>> FOREIGN KEY (`cat_id`) REFERENCES `categories`(`cat_id`) ON DELETE CASCADE,
>> FOREIGN KEY (`subcat_id`) REFERENCES `subcategories`(`cat_id`) ON DELETE CASCADE,
>> PRIMARY KEY(`sub_id`),
>> INDEX `ix_sccat_id`(`cat_id`),
>> INDEX `ix_scsubcat_id`(`subcat_id`)
>> ) ENGINE=InnoDB;

> Your problem is right here:
>
> FOREIGN KEY (`subcat_id`) REFERENCES `subcategories`(`cat_id`) ON
> DELETE CASCADE,
>
> Generally you don't use a foreign key to reference the same table.

Although this design looks a bit weird, a foreign key into the same
table is no problem with InnoDB. In fact it is very useful if you
want to hold simple parent<->child relation in a single table.


XL

Axel Schwenke wrote:
> Jerry Stuckle <jstucklex@attglobal.net> wrote:
>
>>Jim Michaels wrote:
>>
>>>CREATE TABLE `subcategories` (
>>> `sub_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
>>> `cat_id` INTEGER UNSIGNED NOT NULL DEFAULT 0,
>>> `subcat_id` INTEGER UNSIGNED NOT NULL DEFAULT 0,
>>> FOREIGN KEY (`cat_id`) REFERENCES `categories`(`cat_id`) ON DELETE CASCADE,
>>> FOREIGN KEY (`subcat_id`) REFERENCES `subcategories`(`cat_id`) ON DELETE CASCADE,
>>> PRIMARY KEY(`sub_id`),
>>> INDEX `ix_sccat_id`(`cat_id`),
>>> INDEX `ix_scsubcat_id`(`subcat_id`)
>>>) ENGINE=InnoDB;
>
>
>>Your problem is right here:
>>
>> FOREIGN KEY (`subcat_id`) REFERENCES `subcategories`(`cat_id`) ON
>>DELETE CASCADE,
>>
>>Generally you don't use a foreign key to reference the same table.
>
>
> Although this design looks a bit weird, a foreign key into the same
> table is no problem with InnoDB. In fact it is very useful if you
> want to hold simple parent<->child relation in a single table.
>
>
> XL

To some extent that's true. However, it also runs into the problem you
see here. He can't insert because the foreign key doesn't exist yet.

I should have added the solution is you need to remove the constraint,
add the first element, then add the constraint back in.

And BTW - although it might work sometimes, it's generally frowned upon
to reference you own table for this very reason. Another way to do it
is to have a second table with the parent-child relationships.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

> >>>CREATE TABLE `subcategories` (
> >>> `sub_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
> >>> `cat_id` INTEGER UNSIGNED NOT NULL DEFAULT 0,
> >>> `subcat_id` INTEGER UNSIGNED NOT NULL DEFAULT 0,
> >>> FOREIGN KEY (`cat_id`) REFERENCES `categories`(`cat_id`) ON DELETE
CASCADE,
> >>> FOREIGN KEY (`subcat_id`) REFERENCES `subcategories`(`cat_id`) ON
DELETE CASCADE,
> >>> PRIMARY KEY(`sub_id`),
> >>> INDEX `ix_sccat_id`(`cat_id`),
> >>> INDEX `ix_scsubcat_id`(`subcat_id`)
> >>>) ENGINE=InnoDB;
> >
> >
> >>Your problem is right here:
> >>
> >> FOREIGN KEY (`subcat_id`) REFERENCES `subcategories`(`cat_id`) ON
> >>DELETE CASCADE,
> >>
> >>Generally you don't use a foreign key to reference the same table.
> >
> >
> > Although this design looks a bit weird, a foreign key into the same
> > table is no problem with InnoDB. In fact it is very useful if you
> > want to hold simple parent<->child relation in a single table.
> >
> >
> > XL
>
> To some extent that's true. However, it also runs into the problem you
> see here. He can't insert because the foreign key doesn't exist yet.
>
> I should have added the solution is you need to remove the constraint,
> add the first element, then add the constraint back in.

Right. That sounds bad -> modifying metadata to get your data in.

Why not insert a NULL instead and update your inserted row when
you've got your FK data in order?

Oh right, now I see:
CREATE TABLE `subcategories` (
`sub_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ignore',
`cat_id` INTEGER UNSIGNED NOT NULL DEFAULT 0 COMMENT 'copy of cat_id from
categories table',
`subcat_id` INTEGER UNSIGNED NOT NULL DEFAULT 0 COMMENT 'cat_id of
subcategory',
FOREIGN KEY (`cat_id`) REFERENCES `categories`(`cat_id`) ON DELETE
CASCADE,
FOREIGN KEY (`subcat_id`) REFERENCES `subcategories`(`cat_id`) ON DELETE
CASCADE,
PRIMARY KEY(`sub_id`),
INDEX `ix_sccat_id`(`cat_id`),
INDEX `ix_scsubcat_id`(`subcat_id`)
) ENGINE=InnoDB;


"subcat_id" is NOT NULL? How can that be? A sub-category
ALWAYS has to refer to a sub-category? The "default 0" looks
like there's a dummy sub category. That sounds bad as well.

IMO, the better thing to do is modify the design and split up this
table.


--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

Jerry Stuckle <jstucklex@attglobal.net> wrote:
> Axel Schwenke wrote:
>>>Jim Michaels wrote:

>>>Your problem is right here:
>>>
>>> FOREIGN KEY (`subcat_id`) REFERENCES `subcategories`(`cat_id`) ON
>>>DELETE CASCADE,
>>>
>>>Generally you don't use a foreign key to reference the same table.
>>
>>
>> Although this design looks a bit weird, a foreign key into the same
>> table is no problem with InnoDB. In fact it is very useful if you
>> want to hold simple parent<->child relation in a single table.


> To some extent that's true. However, it also runs into the problem you
> see here. He can't insert because the foreign key doesn't exist yet.

That's why I called his design 'weird'. Typically one has no NOT NULL
constraint on the parent id column. So root nodes are inserted with
parent id = NULL.

The other weird point is, that he has a FOREIGN KEY into another table
*and* a FOREIGN KEY into the same table. Usually you have only one of
them. If your nodes are all the same, you put them all into one table.
If some nodes (say: root nodes) are different, you put them in another
table.


XL

I removed the offending foreign key. I could not insert the data into
categoryroots. foreign key problem again. forced to remove the foreign key
from categoryroots table so I could. why?

"Jerry Stuckle" <jstucklex@attglobal.net> wrote in message
news:hKGdnZyCgtS1akrenZ2dnUVZ_tqdnZ2d@comcast.com. ..
> Jim Michaels wrote:
>> I am not sure how to set up the foreign keys on this one, as I've never
>> done
>> foreign keys before (and I'm sure it shows). I have a tree of
>> information.
>> I can insert into the categories table, but not anything else - I get
>> foreign key errors (and I am not sure how to interpret the error).
>> Cannot add or update a child row: a foreign key constraint fails
>> (`db0/subcategories`, CONSTRAINT `subcategories_ibfk_2` FOREIGN KEY
>> (`subcat_id`) REFERENCES `subcategories` (`cat_id`) ON DELETE CASCADE)
>>
>>
>> I am also curious if the foreign key definition in subcategories would
>> cause
>> the database to fail or error on a delete because of the recursion.
>> (that's
>> where the tree is stored).
>>
>>
>> CREATE TABLE `categories` (
>> `cat_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
>> `category` VARCHAR(100) NOT NULL DEFAULT '' COMMENT 'your category
>> here',
>> PRIMARY KEY(`cat_id`),
>> UNIQUE KEY `ix_ccat_id` (`cat_id`),
>> INDEX `ix_ccat`(`category`)
>> ) ENGINE=InnoDB;
>>
>> CREATE TABLE `subcategories` (
>> `sub_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ignore',
>> `cat_id` INTEGER UNSIGNED NOT NULL DEFAULT 0 COMMENT 'copy of cat_id
>> from
>> categories table',
>> `subcat_id` INTEGER UNSIGNED NOT NULL DEFAULT 0 COMMENT 'cat_id of
>> subcategory',
>> FOREIGN KEY (`cat_id`) REFERENCES `categories`(`cat_id`) ON DELETE
>> CASCADE,
>> FOREIGN KEY (`subcat_id`) REFERENCES `subcategories`(`cat_id`) ON
>> DELETE
>> CASCADE,
>> PRIMARY KEY(`sub_id`),
>> INDEX `ix_sccat_id`(`cat_id`),
>> INDEX `ix_scsubcat_id`(`subcat_id`)
>> ) ENGINE=InnoDB;
>>
>> CREATE TABLE `categoryroots` (
>> `root_id` int(10) unsigned NOT NULL auto_increment COMMENT 'ignore',
>> `cat_id` int(10) unsigned NOT NULL default '0' COMMENT 'copy of cat_id
>> from categories table',
>> PRIMARY KEY (`root_id`),
>> FOREIGN KEY (`cat_id`) REFERENCES `subcategories`(`cat_id`) ON DELETE
>> CASCADE,
>> UNIQUE KEY `ix_crcat_id` (`cat_id`)
>> ) ENGINE=InnoDB;
>>
>
> (data snipped)
>
> Jim,
>
> Your problem is right here:
>
> FOREIGN KEY (`subcat_id`) REFERENCES `subcategories`(`cat_id`) ON DELETE
> CASCADE,
>
> Generally you don't use a foreign key to reference the same table. If I
> understand what you're trying to do, referencing categories should be
> sufficient.
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstucklex@attglobal.net
> ==================

but then I can't insert any data.

"Bill Karwin" <bill@karwin.com> wrote in message
news:dr9u620499@enews4.newsguy.com...
> "Jim Michaels" <jmichae3@yahoo.com> wrote in message
> news:5rOdnSBK5PrqbkrenZ2dnUVZ_sydnZ2d@comcast.com. ..
>> Cannot add or update a child row: a foreign key constraint fails
>> (`db0/subcategories`, CONSTRAINT `subcategories_ibfk_2` FOREIGN KEY
>> (`subcat_id`) REFERENCES `subcategories` (`cat_id`) ON DELETE CASCADE)
>
> You can't add to a table a reference to the same table if the table hasn't
> been created yet.
> This is true if the SQL interpreter is just parsing the CREATE TABLE
> statement.
>
> To make self-referencing foreign keys, you have to create the table
> without that foreign key constraint (but do create the field to be used as
> the reference), then use ALTER TABLE to add the constraint.
>
> For example:
>
> CREATE TABLE `subcategories` (
> `sub_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ignore',
> `cat_id` INTEGER UNSIGNED NOT NULL DEFAULT 0 COMMENT 'copy of cat_id from
> categories table',
> `subcat_id` INTEGER UNSIGNED NOT NULL DEFAULT 0 COMMENT 'cat_id of
> subcategory',
> FOREIGN KEY (`cat_id`) REFERENCES `categories`(`cat_id`) ON DELETE
> CASCADE,
> PRIMARY KEY(`sub_id`),
> INDEX `ix_sccat_id`(`cat_id`),
> INDEX `ix_scsubcat_id`(`subcat_id`)
> ) ENGINE=InnoDB;
>
> ALTER TABLE `subcategories` ADD CONSTRAINT
> FOREIGN KEY (`subcat_id`) REFERENCES `subcategories`(`cat_id`)
> ON DELETE CASCADE;
>
> Regards,
> Bill K.
>

Jim Michaels wrote:
> I removed the offending foreign key. I could not insert the data into
> categoryroots. foreign key problem again. forced to remove the foreign key
> from categoryroots table so I could. why?
>

Jim,

When using foreign keys, the item must exist in the referenced table
before you can add it to the current table.

In this case it means to add an item to categoryroots with a cat_id of
1, you need to first have an entry with cat_id of 1 in the subcategories
table.

And please don't top post.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================