Logical Data Modeling - Primary Key (Id, Name)

About

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

• entity
• or a relationship

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:

• from SQL join
• to foreign key

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

• Relational database: Relational Data Modeling - Primary key
• Code: map where the key is the primary key (other implementation may be possible)