Posted by Jim Michaels on 10/13/34 11:38

problem solved.

"Jim Michaels" <jmichae3@yahoo.com> wrote in message
news:hsidneEUPL4KhEreRVn-oQ@comcast.com...
>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]