ForeignKey Referencing Same Table

Question

There was an interview test in which below was the table and structure

 Table Person = id, name, dob, dod, mother_id, father_id
 Primary Key (id)
 Foreign Key mother_id references Person
 Foreign Key father_id references Person

and it was asked

1. "select all who are mothers"
2. "select those child who are children of 'John Smith' and 'Jane'

and I was puzzled because I was assuming that foreign key would be linked with some other table as usual. But at that point I failed. do some one know the actual answer and reason?

Answer 1

This kind of data structure is called a "Self Referencing Table"

SELECT DISTINCT mothers.*
FROM person
    inner join person mothers on person.mother_id = mothers.id

and

SELECT person.*
FROM person
    inner join person fathers on person.father_id = fathers.id
    inner join person mothers on person.mother_id = mothers.id
WHERE 
    fathers.name='john smith'
and 
    mothers.name='jane'

Answer 2

You can always have foreign key that link to the same table.. There is not problem in that..

See, suppose you are storing a person record in the table, now that person would be having mother and father. And mother and father both are themselves a person.. so, they are itself a record of the same table..

Suppose you have following two records in Person table: -

id     name           age         mother_id
1      xyz            24          5
5      abc            57          6

So, from the above table you see that, person with id = 5 is actually mother of person with id = 1.. So, it is a foreign key referencing the same table..

So, here rather than performing a join operation with a different table, you have to perform join with the same table..

SELECT p2.id FROM
Person p1 join Person p2
WHERE p1.mother_id = p2.id

This query will select the record of mother of your current record..

Answer 3

I was assuming that foreign key would be linked with some other table as usual.

It still is - it references other records in the same Person table.

-- All Mothers
SELECT mom.name
   FROM Person mom
   WHERE EXISTS (SELECT 1 FROM Person WHERE mother_id = mom.id);

-- Children of John Smith and Jane
SELECT kid.name
   FROM Person kid
   INNER JOIN Person mom on kid.mother_id = mom.id
   INNER JOIN Person dad on kid.father_id = dad.id
   WHERE mom.name = 'Jane' AND
   dad.name = 'John Smith';

Have a look at this SQL fiddle here