Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: The Case Against Compound/Natural Keys

Re: The Case Against Compound/Natural Keys

From: Nuno Souto <>
Date: Tue, 30 Jan 2007 22:16:57 +1100
Message-ID: <>

Don Seiler wrote,on my timestamp of 30/01/2007 2:23 AM:

> My database is actually more of an all-purpose hybrid.

Just like the majority of dbs out there. Like it or not, OLTP and DW are two extremes. The middle of the road and most common db type is by far the DSS. Which has bits of both plus its own distinctive set of a zillion tables. Then again, I've recently heard someone ask "DSS, what's that?" (must be the "powerpoint OCP" factor again...)

> To be precise, I don't hate natural keys for the sake of hating
> natural keys. It's the composite keys that I hate, and especially
> when there are no queries that such a large index would address.

and when you have those natural composite keys present on a large number of tables as FKs. And indexed, of course, because otherwise joins would be painfully slow.

This is of course the detail that is omitted from the "natural key" camp arguments: it's not just the index on the composite natural PK, it's also the one that will be needed wherever that composite, very long natural key will be used as a FK!

As opposed to a single column index on the synthetic key wherever it is used as a FK, plus a unique index on the composite key in the prime parent table.

I know which overhead I'd rather take.

Yes, I'm painfully aware of the index hot spot problem with growing synthetic key values: been aware of that for many years, even before database engines had an "r" before the "dbms"! It's not a new problem and it has been overcome in other dbms engines: there is no reason it can't in Oracle other than inactivity on the part of their R&D.

There is nothing in indexing b-tree theory that says the top level index node has to be stored in one single block, for example. In fact, there is nothing in b-tree indexing theory that requires the concept of "blocks": that's a physical storage requirement, not a logical one necessary for the operation of b-tree indexes.

But even with that problem present, I'll gladly take the trade-off and use synthetic keys anywhere and everywhere rather than having to incur the horrible maintenance and space overhead that is natural composite keys used as PKs *AND* as FKs.

Nuno Souto
Received on Tue Jan 30 2007 - 05:16:57 CST

Original text of this message