Data Modeling - Identifier - (Id|Primary|Natural|Surrogate|Business) key

1 - About

Every entity must have a key (also known as an identifier). A key is an attribute, or a group of attributes, which assumes a unique value for each entity instance.

A group of attributes that uniquely identifies an instance of an entity is called a concatenated key.

A primary key is that candidate key which will most commonly be used to uniquely identify a single entity instance.

Any candidate key that is not selected to become the primary key is called an alternate key.

Same concept, see also: Security - Identity (id of an entity)

3 - Example

4 - Identifier

An identifier data type must be able to hold any key value.

A common implementation of this data type will be an integer. However, the column values can exceed the range of integer values supported by a 4-byte integer and other data type such as as 8-byte integer, decimal or character string may be needed.

5 - Keys

An Foreign Key is not a identifier but enforces a relationship. See Data Modeling - Foreign Key Constraint

5.1 - Primary

5.2 - Surrogate

5.3 - Natural

If the table has a true primary key – it is known as a “natural key”

5.4 - Business

Unique key that use the business users to identify uniquely an entity. The business key is a meaningful/business code based generally on the concatenation of attributes value of an entity instance.

6 - Reference

data/modeling/key.txt · Last modified: 2018/04/18 14:40 by gerardnico