SPS Home    >   Dgreath    >   RDBMS    >   Table Keys

TABLE KEYS

Keys are central to the relational database concept as they make it possible to refer to instances (rows) within the relation (table).

Candidate Key -- Any attribute (column) capable of identifying a row within the table

Primary Key -- The column in a table intended to uniquely identify each row

  • Must be unique (no duplicates)
  • Must never contain a NULL value
  • Must be persistent (never changes once created)
  • All columns in the table must depend on the key in varying degrees
Foreign Key -- A column in another table that links to a candidate key via a join.
  • Need not be unique (duplicates possible) and duplication frequently necessary
  • Shouldn't contain a NULL, however, a null condition shouldn't break the application
  • Should be reasonably persistent

Composite Key -- A key made up of two or more columns to ensure unique selection of records when no table attribute (column) can ensure uniqueness standing alone. A composite is used as an alternative to creating a key to conserve table size.

Natural vs. Artificial Keys -- Essentially there are two approaches to creating a key--natural or artificial. A natural key is one selected from the data in table (such as a social security number) where the data is guaranteed to be non null, unique, and persistent either alone or in combination with other data. In circumstances where a natural key isn't practical, an artificial key will need to be created.  Best practice is to use either a Universal Unique ID (UUID) or a Globally Unique ID (GUID) [the MS version of a UUID] which will guarantee all three conditions are met. If the system does not provide either UUID or GUID generation, a sequential serial number can be used but has limitations.

UUIDs used for Artificial Keys -- A basic time generated UUID is a 16 byte (128 bit) concatenation of the number of 100 nanosecond intervals from the creation of the Gregorian calendar 15 October 1582  at 00:00:00.00 (timestamp), a clock ID, and computer's MAC address, all expressed in hexadecimal, typically expressed in five fields of four bytes, two bytes, two bytes, two bytes, and six bytes, thus: 

e3d042a0-1f21-11db-a98b-0800200c9a66

TIMESTAMP(lsb)-TIMESTAMP(mid byte)-TIMESTAMP(msb)-CLOCKID-MACADDRESS

 The clockID is a serial number that is incremented each time the generator is unsure of its uniqueness (for example when the computer's internal clock is reset).

The UUID system becomes invalid in 3400AD when the timestamp outgrows the space allocated for it.

For more information about UUIDs, see RFC-4122.

Many RDBMSs provide a means of generating UUIDs natively.