Re: Choosing a surrogate key vs. a composite key - "holy war" issue or clear best practice?

From: Frank van Bortel <>
Date: Sat, 28 Jun 2008 15:15:12 +0200
Message-ID: <2cc13$48663961$524b5c40$> wrote:
> Early in my career, I was accustomed to seeing simple, sequence-
> generated surrogate keys. Seemed clean and logical. Later in my

They are not. They're called technical keys for a reason. They are invented by technocrats.

> career, I saw seemingly monstrous (to me) composite keys of up to 5
> fields. Found them initially repulsive because they made queries
> difficult to read and write. I'm wondering if there is a clear best
> practice on choosing a surrogate key over a composite key--or if this
> is a "holy war" issue no one (collectively) currently agrees on.
> Some pros to Composite primary keys:
> --------------------------------------------------------
> * They enforce uniqueness; meaning you could have a sequence-generated
> surrogate key, yet redundant rows may still creep into your table.
> They will be uniquely identifiable in that, yes, the surrogate key
> values are unique; but all other column values in > 1 rows could be
> identical.

You do not seem to know the difference between Primay and Unique keys

> * Simpler in the aspect of not having to create, manage, and have
> programmers reference a surrogate key sequence

Yeah - let's keep it simple. Lets call all PK "ID".
> * Can't think of any other Pros to composite keys. Anyone?

They are logical.
Because of that, they are self-documenting.

> Some cons to Composite primary keys:
> ---------------------------------------------------------
> * Composite PKs with a large # of columns make queries difficult to
> read and write

Well, learn how to read and write!
> * Composite PKs with a large # of columns makes referencing tables
> larger; e.g. a foreign key in a child table is a copy of a composite
> PK in a parent table. This means more data storage is required in
> referencing child tables and the burdens that presents.

Storage - who cares? I don't, but I know some employers do. They just cannot understand many cheap disks will be as fast as many expensive "enterprise-class" disks. No - those disks rotate faster, so the "have less latency" - yeah, and so what?

> * Some applications, like ESRI's ArcGIS Desktop, seem not to be
> composite key aware, e.g. to those who know the product, you seemingly
> can't do a "relate" to any existing external tables that use composite
> keys; this makes database integration with legacy relational and
> current relational databases, difficult. This may be the case
> generally in OO programming and approaches and not at all unique to
> ESRI--the presumption that all primary keys are only ever composed of
> a single-column.

ESRI and Oracle is not a match, made in heaven. I understand they are in therapy, so who knows...

> Some pros to surrogate primary keys:
> -------------------------------------------------------
> * Aside from the obvious ones, given the cons I've listed for
> composite keys, it seems you can have a surrogate key yet still
> enforce more than nominal row uniqueness by creating a unique
> constraint/index on the fields you'd otherwise.
> * Sometimes there's no good combination of keys for making a composite
> primary key

Then, redo your (home)work, and apply analysis.

> What have I missed in this debate?

There is no debate. You're mumbling to yourself, and throwing flamebait. This seems to happen every now and them - searching the archives is not difficult, but hey, getting your name out is far more important.


Frank van Bortel
Received on Sat Jun 28 2008 - 08:15:12 CDT

Original text of this message