Search code examples
databasedatabase-designprimary-keykey

Surrogate vs. natural/business keys

Here we go again, the old argument still arises...

Would we better have a business key as a primary key, or would we rather have a surrogate id (i.e. an SQL Server identity) with a unique constraint on the business key field?

Please, provide examples or proof to support your theory.

Solution

  • Both. Have your cake and eat it.

    Remember there is nothing special about a primary key, except that it is labelled as such. It is nothing more than a NOT NULL UNIQUE constraint, and a table can have more than one.

    If you use a surrogate key, you still want a business key to ensure uniqueness according to the business rules.