Surrogate vs. natural/business keys

database database-design primary-key key
Manrico Corazzi picture Manrico Corazzi · Sep 15, 2008 · Viewed 68.9k times · Source

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.

Answer

Jay Shepherd picture Jay Shepherd · Sep 15, 2008

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.

Related questions

Can I have multiple primary keys in a single table?

Can I have multiple primary keys in a single table?

Read More
Strings as Primary Keys in SQL Database

I am not very familiar with databases and the theories behind how they work. Is it any slower from a performance standpoint (inserting/updating/querying) to use Strings for Primary Keys than integers?

Read More
Why is negative id or zero considered a bad practice?

Why is negative id or zero considered a bad practice when inserting a primary key in a database table? I think it could be useful in some cases, but people say that it is not recommended, despite the fact that …

Read More