A Key to Keys

A reference for all the different types of keys used in logical and physical database design.

These tables will be used to illustrate all the different keys:
A Key to Keys ERDA Key to Keys Data

Superkey

A superkey is any group of columns that can uniquely identify a row.

Let SK be a subset of the heading of relvar R. Then SK is a superkey for R if and only if it possesses the following property:

1. Uniqueness: No valid value for R contains two distinct tuples with the same value for SK.
Date, C.J. Database Design & Relational Theory. Sebastopol: O,Reilly Media, Inc., 2012. Print. (Emphasis theirs)

All the columns of the table taken together are a superkey, assuming there are no duplicate rows (and there shouldn’t be if you are in 1NF!)

The Product table has the following superkeys:

{ProductId, Product, Category}, {ProductId, Product}, {ProductId, Category}, {ProductId}, {Product}

The ProductSupplier table has the superkeys:

{ProductId, Supplier, Price}, {ProductId, Supplier}

Candidate Key

A superkey that is irreducible is a candidate key. It is a minimal set of columns that can uniquely identify a row. Candidate keys are usually referred to as just ‘keys’.

Let K be a subset of the heading of relvar R. Then K is a candidate key (or just key for short) for R if and only if it possesses both of the following properties:
1. Uniqueness: No valid value for R contains two distinct tuples with the same value for K.
2. Irreducibility: No proper subset of K has the uniqueness property.

Date, C.J. Database Design & Relational Theory. Sebastopol: O,Reilly Media, Inc., 2012. Print. (Emphasis theirs)

A table can have more than one candidate key. The Product table has two:

{ProductId}

{Product}

ProductSupplier has one:

{ProductId, Supplier}

Subkey

Any field or combination of fields that are included in a set that is a candidate key are subkeys.

Let SK be a subset of the heading of relvar R. Then SK is a subkey for R if and only if it’s a subset of at least one key of R.
Date, C.J. Database Design & Relational Theory. Sebastopol: O,Reilly Media, Inc., 2012. Print. (Emphasis theirs)

Product has the following subkeys:

{ProductId}, {Product}

ProductSupplier has these subkeys:

{ProductId, Supplier}, {ProductId}, {Supplier}

Composite Key

A composite key is a candidate key that is made up of more than one attribute.

ProductSupplier has a composite key:

{ProductId, Supplier}

Surrogate Key

A meaningless value added to uniquely identify a row. Usually this is an identity or ‘autonumber’ column.

The Product table has a surrogate key:

{ProductId}

Surrogate keys are commonly added and marked as the primary key when a table has a non-numeric or composite key so other tables that reference it can be joined more efficiently. Joining two single integer fields between tables is more efficient than joining multiple fields or joining non-numeric data types.

Natural Key

A key that is made up of naturally occurring fields in the table, as opposed to a surrogate key.

Product has a natural key:

{Product}

In ProductSupplier, although ProductId is a surrogate key, it comes from another source. It is not a surrogate key for rows in the ProductSupplier table. ProductSupplier is the intersection of product ids and suppliers, and it has a natural key:

{ProductId, Supplier}

Primary Key

A candidate key that has been selected for some special purpose in the database. The system enforces a unique constraint on the columns selected.

This is a database feature, and does not play any special role in normalizing tables. It is usually selected with the intention that it is the column that will be used as a foreign key in child tables.

Some resource materials call candidate keys that are not selected as a primary key ‘alternate keys’.

{ProductId} is the primary key of Product.

{ProductId, Supplier} is the primary key of ProductSupplier.

Foreign Key

An attribute that references another attribute, typically in another table. The referenced attribute must exist or else the foreign key is considered ‘violated’.

Database systems usually perform a check that the referenced value exists before updating/inserting the value in the referencing table. This is called referential integrity. An update or insert that attempts to violate the foreign key will fail as long as referential integrity is enabled.

Most database systems require the referenced field(s) of a foreign key to be either a primary key or in a unique constraint.

In ProductSupplier, {ProductId} is a foreign key that references the {ProductId} field of the Product table. Any value for {ProductId} that exists in ProductSupplier must first exist in Product.

One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s