I've got a quite simple question about natural/surrogate key usage inside a well-defined context wich manifests itself frequently, which i am likely to illustrate.

Let us assume you're creating the DB schema for any product using SQL Server 2005 as DBMS. With regard to simplicity let us say you will find only two organizations involved, wich happen to be planned to two tables, Master and Slave. Think that:

  1. We are able to have ..n Slave records for any single Masters row
  2. Column set (A, B, C, D) in Master is the only real candidate for primary key
  3. Column B in Master is susceptible to changes with time
  4. A, B, C, D really are a mix of varchar, decimal and bigint posts.

Now you ask ,: how does one design secrets/constraints/references for individuals tables? Can you rather (argumenting your decision):

  1. Implement an amalgamated natural key on Master on (A, B, C, D), along with a related composite foreign key on Slave, or
  2. Introduce a surrogate key K on Master, let say an IDENTITY(1,1) column having a related (single column) foreign key on Slave, adding a distinctive constraint on Masters (A, B, C, D), or
  3. Make use of a different approach.

For me I'd opt for option 2), mainly due to assumption 3) and gratifaction-smart, but Let me hear another person's opinion (since there's quite a wide open debate around the subject).


Your assumption (3) has a tendency to suggest option (2) since it is bothersome and potentially time intensive to cope with cascading down updates from the primary key of Master when B changes.

Obviously it is dependent how frequently this can occur: if it's something you be prepared to happen "constantlyInch it indicates (A,B,C,D) is really a poor selection of primary key however, whether it is only going to rarely happen, then (A,B,C,D) might be the ideal choice of primary key, and getting individuals posts in Slave might have some advantages (you don't need to join to understand constantly to discover individuals column values).

I'd choose option 2. Make it simple.

It ticks the boxes (narrow, number, constant, strictly monotonically growing) for any helpful clustered index (the default of PKs in SQL Server).

You have to pressure the originality on A,B,C,D, though, to preserve data integrity, as noted.

There's nothing conceptually wrong with option 1, but the moment you need more indexes on "master" then your wide clustered key turns into a liability. Or even more work to find out which index is better as clustered.


Just in case associated with a confusion

the option of which index is clustered is separate to the option of key

Either 1,two or three. There is not always enough information to find out whether a surrogate is essential or how helpful it may be. Are the compound key characteristics also a part of some key or constraint within the Slave table? Can there be another key of Master that may be used like a foreign key? The truth that a vital value may change should not function as the determining factor because any key value might need to change - surrogates aren't any exception.

there's quite a wide open debate around the subject

Regrettably, a lot of that debate is dependant on the mistaken assumption you need to choose from whether surrogate or perhaps a natural key. As the option 2 appropriately indicates you should use both because the need arises. One isn't a replacement for another because simple secrets and compound secrets on different characteristics clearly mean various things inside your data model and enforce different constraints in your data.