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: jaromir nemec <>
Date: Sun, 28 Jan 2007 23:47:29 +0100
Message-ID: <07af01c7432e$52814a70$3c02a8c0@JARAWIN>


> These tables are bulk-loaded and .

I assume your database is a kind of DW system.

> They've all heard me calling for
> surrogate keys, but they say they need uniqueness among this set of
> fields. Then when they discover duplicates, they just add another
> field.

I thing you address two different problems here: a) how to enforce the uniqueness of a fact table b) how to define the primary key (natural / surrogate) on the dimension table

Uniqueness of a fact table can be enforced using index, alternatively you may define a cleaning step in the loading process (eliminating the dups before the load) and not to rely on an index. A similar pre-processing step can enforce the consistency of the FK relation to the parent table.

For a dimensional table (your "parent table") there are two options in my opinion
a) use natural key as a primary key of the dimension and a foreign key of the fact table - it is your implementation b) use surrogate key for PK of the dimension and FK of the fact table and additionally denormalize the dimension natural key into the fact table. There is a nice example on Jonathan Lewis blog demonstrating the consequences of using "pure" surrogates. When to use surrogate keys? It depends on the "nature" of the natural keys. A little example: I wouldn't for sure set up a DW with natural key (only) for Oracle product names. Querying webDB, htmlDB, RAC,. over years of history would be a nightmare.
A real value added surrogate key processing must implement some logic deciding when to assign a new key (for a new dimension instance) or to reuse existing one (for a new version of changed dimension instance).

HTH Jaromir D.B. Nemec
----- Original Message -----
From: "Don Seiler" <>
To: "oracle-l" <>
Sent: Saturday, January 27, 2007 5:48 AM Subject: The Case Against Compound/Natural Keys

Received on Sun Jan 28 2007 - 16:47:29 CST

Original text of this message