Assuming I have the following tables.
PARENT: PARENT_ID serial, DESCRIPTION character varying(50)
CHILD: PARENT_ID integer, CHILD_ID integer, DESCRIPTION character varying(50)
What I would like to see is each row in CHILD having a CHILD_ID that starts at 1 and increments by 1, unique per PARENT_ID. It would be similar to a revision number. For example..
PARENT_ID 1, CHILD_ID 1
PARENT_ID 1, CHILD_ID 2
PARENT_ID 1, CHILD_ID 3
PARENT_ID 2, CHILD_ID 1
PARENT_ID 3, CHILD_ID 1
PARENT_ID 3, CHILD_ID 2
Is there any way to have the CHILD_ID value assigned automatically, such as a sequence or constraint, only with the ability to reuse a CHILD_ID that has been deleted? The only way I can figure out is something to the effect of this SQL.
INSERT INTO child SELECT parent_id, MAX(child_id)+1, 'description' FROM child WHERE parent_id = :PARENT_ID GROUP BY parent_id
That's a bit of a hack though. I realize that database normalization suggests you should not have one key related to another, but I don't have that option for some other reasons. Any ideas?
EDIT: The title's ugly. If any of you high-scoring folks can think of one that's more accurate, please feel free to change it.