question: internal relationships in InnoDB tables

[sfullman]

I recently upgraded to mySQL 4, and use phpmYAdmin 2.5.6. What a great improvements to cascade update and delete! I'm very happy. However there is one problem:

For tables where there is a heirarchy of records (like a table named forum_posts), let's say the primary key is called id. To indicate the heirarchy, I add a column called tablename_primary, or in this case forum_posts_id.

This field represents the id of the PARENT post, so I can construct a tree:

ID ForumPosts_ID
1 0
2 0
3 1
4 1
5 4

so 1 and 2 are root level posts, 3 is a child of 1 and so is 4, and 5 is a child of 4.

OK, but the problem is setting up the relationship. Because, when I try to enter a root-level record, I can't because it has no parent. MySQL returns "update fails"..

I suppose I could add a "dummy record" with id=0 but this seems sloppy. Any more precise method of dealing with this relationship in InnoDB? The objective is, deleting a record would also delete all it's children in the table (which is anoterh reason I don't want a "zero" record cause deleting by accident would empty the table!)

Thanks,
Sam

[Sxooter]

Use NULL as the key there. You can use NULL as long as the field isn't declared NOT NULL.

[sfullman]

OK, I'll use that, but I see why you held your picture back for so long :-) (That's why I don't post mine either :-)

Colorado, huh? Not too far from California, I live in Los Angeles. Thanks for your assistance.

Sam Fullman
Compass Point Media