Curated SQL Posts

Lee Asher digs into keys:

Keys come in two basic flavors: natural and surrogate. Natural keys, as their name suggests, are values that occur naturally in the data. They have real-world or business meaning: an email address, the street address of a building, the serial number of an inventory item, etc. In contrast, a surrogate key (sometimes called a synthetic key) is a value added to each row upon creation. A surrogate exists only within the confines of the database; it has no meaning outside of it.

A natural key often contains two or more columns. For instance, the key for a table of vehicle types might include the make, model, year, and trim level, all four columns of which must be combined to create the value that uniquely identifies each row. Surrogate keys are always a single column, though the value of each key may be anything you choose – as long as each value is distinct from all others.

One thing I would very strongly note here is that surrogate keys are a physical data model concept. I’m a firm believer that you almost always should have a surrogate key, but there must be a natural key, even if you don’t put an actual constraint on it. Though I do recommend having a unique key constraint on the natural key as well as a primary key constraint on the surrogate key.