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 the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial