Choosing a Primary Key: Natural or Surrogate?

Surrogate Key 라는 것을 처음 들어봤다. 난 무식하다. This article overviews strategies for assigning primary keys to a table within a relational database.  In particular, it focuses on the issue of when to use natural keys and when to use surrogate keys.  Some people will tell you that you should always use natural keys and others will tell you that you should always use surrogate keys.  These people invariably prove to be wrong, typically they're doing little more than sharing the prejudices of their "data religion" with you.  The reality is that natural and surrogate keys each have their advantages and disadvantages, and that no strategy is perfect for all situations.  In other words, you need to know what you're doing if you want to get it right.  This article discusses: Common key terminology   Strategies for assigning keys Surrogate key implementation strategies Tips for effective keys What to do when you make the "wrong" choice

1. Common Key Terminology

Let's start by describing some common terminology pertaining to keys and then work through an example.  These terms are: Key. A key is one or more data attributes that uniquely identify an entity.  In a physical database a key would be formed of one or more table columns whose value(s) uniquely identifies a row within a relational table.  Composite key.  A key that is composed of two or more attributes.  Natural key.  A key that is formed of attributes that already exist in the real world.  For example, U.S. citizens are issued a Social Security Number (SSN)  that is unique to them (this isn't guaranteed to be true, but it's pretty darn close in practice).  SSN could be used as a natural key, assuming privacy laws allow it, for a Person entity (assuming the scope of your organization is limited to the U.S.).  Surrogate key.  A key with no business meaning. Candidate key.  An entity type in a logical data model will have zero or more candidate keys, also referred to simply as unique identifiers (note: some people don't believe in identifying candidate keys in LDMs, so there's no hard and fast rules).  For example, if we only interact with American citizens then SSN is one candidate key for the Person entity type and the combination of name and phone number (assuming the combination is unique) is potentially a second candidate key.  Both of these keys are called candidate keys because they are candidates to be chosen as the primary key, an alternate key  or perhaps not even a key at all within a physical data model.  Primary key.  The preferred key for an entity type. Alternate key. Also known as a secondary key, is another unique identifier of a row within a table.  Foreign key. One or more attributes in an entity type that represents a key, either primary or secondary, in another entity type.

- See more at: http://www.agiledata.org/essays/keys.html#sthash.IkmtYjTe.dpuf