[jsutton]

Hi,

I have a database holding family tree data.
I have a table called person, with all relevent details about them personally (name, date of birth, birthplace, gender)

I need some way of managing relationships bewteen the people in my database. I would like to have another table made of a compound foreign key, with two personID's from the person table.

Is this possible? or are there any obvious workarounds?
I will be implementing the database using hsqldb.

Thanks in advance
Joe Sutton

[AdaHacker]

By definition, a compound key consists of two or more columns. The idea of having a "compound" key that uses the same column twice doesn't make sense. What would that even mean in terms of enforcing the constraint?

Fortunately, it sounds like what you're looking for isn't a single compound key, but two different foreign keys that both reference the same column. There should be no problem with doing that.

[Ionut]

Another table with parentID and ChildID, both referencing Person should be enough to accomplish what you want. A column can be referenced by any number of tables. Just make sure to put a constraint or something(I don't know about hsqldb) to avoid having the same id on parentId column and ChildId column