The most natural of all keys

I sometimes hear, as an argument for using surrogate keys, that there is
no natural key for a table of persons. There is. It's a compound key
consisting of your mother's primary key and a sequence number telling
which of her chldren you are. In SQL:

create table person
parent integer[],
sequence int,
name varchar(100),
primary key (parent, sequence)

insert into person (parent, sequence, name) values ({}, 0, 'Lucy');
insert into person (parent, sequence, name) values ({0}, 0, 'Albert');
insert into person (parent, sequence, name) values ({0}, 1, 'Betty');
insert into person (parent, sequence, name) values ({1,0}, 0, 'Zim');

Here, Lucy is the first human ever (she has no human ancestor). Albert
and Betty are her children, and Betty has a son, Zim.

The only problem with this is that the foreign key cannot be
described in SQL. It actually has nothing to do with it being a
self-referencing key, but that the parent and
sequence columns of the referenced row should be
parts of the parent column of the referencing row. Something
like the following: foreign key (parent[1], parent[2:]) references person
(sequence, parent)
. Unfortunatly, only column names are permitted
in foreign key constranits, not arbitrary expressions. Implementing
the constraint as a CHECK instead is left as an excercise to
the reader.


  1. For good or bad, keys with structure can be operated on, parsed and transformed. This is a good thing in LDAP and maybe even for URIs, it is usually a bad thing when you decide to change the schema in a relational database.

  2. Oh and I forgot to say, yes of course you are right, that is the most natural of all keys in a DAG.

  3. I hadn't though of that, but of course the family tree is a tree. And as you say, the natural key for a node in a tree is the path.


Note: only a member of this blog may post a comment.