2010-03-11

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.

3 comments:

  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.

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

    ReplyDelete
  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.

    ReplyDelete