2.11. Key Types

The logical and physical keys for a table can be classified by their behavior and their source. Table 2.1 is a quick table of my classification system.

Table 2.1. Classification System for Key Types
                              Natural Artificial "Exposed  Surrogate
                                                  Physical
                                                  Locator"
=====================================================================
Constructed from attributes |
in the reality              |
of the data model           |  Y         N         N          Y
                            |
Verifiable in reality       |  Y         N         N          N
                            |
Verifiable in itself        |  Y         Y         N          N
                            |
Visible to the user         |  Y         Y         Y          N

2.11.1. Natural Keys

A natural key is a subset of attributes that occur in a table and act as a unique identifier. The user sees them. You can go to external reality and verify them. Examples of natural keys include the UPC codes on ...

Get Joe Celko's SQL for Smarties, 3rd Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.