使用表列两次作为复合外键 [英] Using a table column twice as compound foreign key

问题描述

我有一个保存家谱数据的数据库.
我有一个名为person的表，其中包含与他们有关的所有个人详细信息(姓名，出生日期，出生地，性别)

我需要某种方式来管理数据库中的人之间的关系.我想要另一个由复合外键构成的表，其中有来自person表的两个personID.

这可能吗?还是有任何明显的解决方法?
我将使用hsqldb实现数据库.

在此先感谢
Joe Sutton

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

推荐答案

当然，您可以有一个这样的表！让我们将关系(表)称为"DirectAncestors":

Of course you can have a table like that! Let''s call the relation (table) "DirectAncestors" for example:

Table DirectAncestors:
PersonId bigint; (Foreign key to table Person.ID)
FatherID bigint; (Foreign key to table Person.ID) 
MotherID bigint; (Foreign key to table Person.ID)

表Person应该在标有*:
的字段上具有自动递增的主键和唯一索引.

The table Person should have an autoincrement primary key and a unique index on the fields marked *:

Table Person:
ID, (autoincrement)
GivenName, *
Name, *
DOB, *
DOD, (nullable),
Birthplace, *
Gender *

您还可以使表Person与其自身具有外键关系:

You can also have the table Person have a foreign key relation to itself:

Table Person:
ID (autoincrement)
GivenName *
Name *
DOB, *
DOD (nullable),
Birthplace, *
Gender *, 
MotherID, ( Foreign Key to Person.ID nullable ),
FatherID, ( Foreign Key to Person.ID nullable )

当您想双向遍历祖先时，首选第一种解决方案.


希望对您的决定有所帮助.

干杯！

The first solution is preferable when you want to traverse the ancestry in both directions.


Hope that help you a little in your decision.

Cheers!

这篇关于使用表列两次作为复合外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！