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.

Best Answer

Just a few reasons for using surrogate keys:

  1. Stability: Changing a key because of a business or natural need will negatively affect related tables. Surrogate keys rarely, if ever, need to be changed because there is no meaning tied to the value.

  2. Convention: Allows you to have a standardized Primary Key column naming convention rather than having to think about how to join tables with various names for their PKs.

  3. Speed: Depending on the PK value and type, a surrogate key of an integer may be smaller, faster to index and search.