1

I have two tables:

Person(personID, name, phone, email);
Relation(child_personID, parent_playerID);

The relationship table helps identify children and their parent but to do this the personID from the person table has to be referenced twice as foreign. How exactly would I go about doing this?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
batsta13
  • 549
  • 2
  • 12
  • 26
  • Does this answer your question? [Two foreign keys referencing the same primary key](https://stackoverflow.com/questions/11284428/two-foreign-keys-referencing-the-same-primary-key) – philipxy Dec 16 '19 at 04:49

1 Answers1

2

Could look something like this.

create table Person
(
  personID int primary key,
  name varchar(50),
  phone varchar(50),
  email varchar(50)
)

create table Relation
(
  child_personID int references Person(personID),
  parent_playerID int references Person(personID),
  primary key (child_personID, parent_playerID)
)
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
  • So will this return records from the relation table, no matter which column the match is on? – OpenCoderX Jun 06 '12 at 15:43
  • Not sure what you mean. This is the table structure with foreign key constraints that makes sure you don't add rows to `relation` table that does not have a related row in `Person`. To return rows you need a query and what rows that query returns depends on how you write your query. – Mikael Eriksson Jun 06 '12 at 15:48