Posted by Jim Michaels on 01/25/06 08:24

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);

[Back to original message]