CODEWITHSUNDEEP
sqldatabase
Bartłomiej Sobieszek
Bartłomiej Sobieszek

Reputation: 2800

Should this table be self-referencing?

I was never doing self-referencing tables (I didn't dive into it yet), but accidentally I went to this moment I guess

Let's say that we have a table named "Human"

// [Human]
ID
Name
Surname
...
father

The father column is also Human (captain obvious) instance, so father column should be a reference to [Human].ID ?

For some reason it is hard to imagine for me, first occurence of INSERT can't contain reference to itself so father should be nullable (let's dont go into logic behind this, some data can be unknown). This eliminates paired PK columns in table

I made more complex image of this enter image description here

Upvotes: 0

Views: 61

Answers (2)

e-neko
e-neko

Reputation: 1266

You can insert a single row for 'Unknown' person, then Humans with unknown father can reference Unknown's ID

Upvotes: 1

Joel Coehoorn
Joel Coehoorn

Reputation: 415690

Unless you plan to store data on every human who ever lived, you're eventually going to have someone without a father record (and even then: who is your first human's father). In other words, yes, the column needs to be nullable.

Upvotes: 0

Related Questions