Single Identity column and composite key, which to make primary?

Question

I've got a table that's a working copy subset of a base table. The base table has a composite key with a clustered index. However I need a single column unique identifier for my framework to work with the table. Both keys are completely unique, and I'll need to make a clustered, unique index on the original, composite key.

Should I keep the composite key as a clustered primary key, or should I use the single column identifier as a non-clustered primary key? Though my framework requires a single identifying column I don't actually have to specify it as the primary key in the DB layer, so I'm not sure which is preferable.

I'm using SQL Server 2005 and the single column identifier will be an IDENTITY.

Answer 1

This seriously depends on the expected half-life of your project, on how agile your environment is and so on. If your project is there to last, and changes are possible, I would not assume that your composite PK is never going to change. I have seen too many projects burned by such assumptions.

One good example would be the use of Social Security Number. Long ago it was a common practice to use it as a natural PK. Later on, many systems had to restrict its use for privacy reasons, and it became not possible to propagate SS#s all over child tables.

The systems that used identities adjusted to the change easily. The systems that used SS#s in child tables had to go through a major overhaul.

So, in many cases it is cheaper to use identities as PKs, adding a UNIQUE constraint on what currently is considered to be unique, and referring child tables to ParentID. This way we have to do less work to adjust when the situation changes.

Answer 2

It does not matter.

Assuming you set attributes 'clustered' and 'not null' explicitly and independently (rather than using 'primary' to imply them) then 'primary' is merely a designation. The choice of which candidate key to award this designation to is arbitrary.

It sounds to me that you have no objective criteria on which to base the choice.

Of course, folk do apply subjective criteria. Some will always favour the surrogate key (if one exists). Some will favour the key (most) used to reference the table in foreign keys. Some will insist that every table must have a primary key. However, all such actions are done merely for psychological reasons (e.g. following convention or what their college tutor taught them) which is often important only to them.