About

A Primary Key is a key that identify indefinitely uniquely an element:

in their local scope.

It's a part of the fully qualified identifier.

Characteristic

Immutable

A real primary key is immutable as it should identify the entity for ever.

Note that if the data is:

  • meaningful,
  • visible (to end users)
  • external such as natural key.

the data will change in some time and are not truly good candidate for a real primary key.

That's why the primary key should be generated (surrogate key)

Storage

The less, the beter has a primary key is used everywhere:

Generally a primary key is stored via a traditional 4-byte index value.

Data Type

Numeric

A numeric primary key is called an id

Textual

A textual primary key is called a name

Type

Entity

A entity key is a key that:

  • identify uniquely a single entity
  • doesn't change over time. If the primary key has a chance – however remote – of changing, well, then it is NOT the primary key.
  • define, in dimensional modeling, the lowest level of an entity

This is one or a group of attributes such that the relationship between the entity set and the attribute value sets has a one-to-one cardinality.

Relationship

The primary key of a relationship is the primary keys of the involved entities.

Type

There are two fundamental types of primary keys:

  • natural
  • and surrogate (artificial/generated)

Synthetic / Surrogate key

What is a Surrogate Primary key? known also as Substitute, Synthetic or Generated Key - Logical Data Modeling - - A synthetic key is a generated one.

Natural

What is a natural key in data modeling

Implementation