Surrogate vs. natural/business keys

databasedatabase-designkeyprimary-key

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:

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.
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.
Speed: Depending on the PK value and type, a surrogate key of an integer may be smaller, faster to index and search.

Related Solutions

Javascript – How to efficiently count the number of keys/properties of an object in JavaScript

To do this in any ES5-compatible environment, such as Node.js, Chrome, Internet Explorer 9+, Firefox 4+, or Safari 5+:

Object.keys(obj).length

Browser compatibility
Object.keys documentation (includes a method you can add to non-ES5 browsers)

Have multiple primary keys in a single table

A Table can have a Composite Primary Key which is a primary key made from two or more columns. For example:

CREATE TABLE userdata (
  userid INT,
  userdataid INT,
  info char(200),
  primary key (userid, userdataid)
);

Update: Here is a link with a more detailed description of composite primary keys.

Best Answer

Related Solutions

Javascript – How to efficiently count the number of keys/properties of an object in JavaScript

Have multiple primary keys in a single table

Related Topic