Difference between primary key and unique key

sql

database-design

data-integrity

key-concepts

byNikita Barsukov·Oct 18, 2024

A primary key (PK) is a unique identifier for each record and cannot be null. Each table can have only one primary key. This key is used to preserve data integrity and typically creates a clustered index, reordering the way data is stored for better retrieval efficiency.

A unique key (UK) asserts uniqueness across its entries but allows one null value. Because of this, a table can harbor multiple unique keys. Though it also creates an index, a UK generates a non-clustered index, which doesn't alter data storage but can accelerate search speed.

Here's a SQL definition for reference:

CREATE TABLE Employees (
    EmployeeID int NOT NULL, -- Not-null, unique (PK-like employee badges!)
    Email varchar(255) UNIQUE, -- One memorable joke per e-mail allowed 🃏
    CONSTRAINT PK_Employee PRIMARY KEY (EmployeeID)
);

EmployeeID is the PK. It defines unique and not-null constraints. Email is the UK, asserting uniqueness but can be null occasionally.

Fleshing Out Key Functions

Role of primary and unique keys

A primary key anchors each row, reliably ensuring unique data and maintaining the nucleus of referential integrity in database relationships. Think of it as the immutable secret code, different for each row.

A unique key helps enforce uniqueness, but with the allowance of a single null value, which results in a more lenient uniqueness inspection than the PK. It ensures exclusivity but also uniquely accommodates entries that haven't accumulated data yet.

Keys’ indexing effect

Assigning a primary key leads to the automatic creation of a clustered index by most databases, ordering the rows logically around the PK. This optimization improves retrieval speed due to the physical adjacent storage of logically related data.

Conversely, a unique key, apart from creating a non-clustered index, doesn't influence physical storage order. Instead, it maintains a sorted "directional signpost" for more efficient searches.

Key selection process

Selecting a PK or UK requires considering data requirements. Primary keys are paramount in defining relationships and influxes due to foreign key references. Unique keys, however, can enforce extra unique constraints without the strictness of PKs.

Digging Deeper: When to use each key

Choose primary keys (PK):

🛠 When dealing with core data where null entries are unacceptable
🌐 To establish relationships between tables via foreign key constraints
🔒 To guarantee the extreme consistency and data integrity

Opt for unique keys (UK):

☑ To enforce non-duplication on fields that aren't primary identifiers
⚖ On fields that should be unique but can reserve space for future updates (e.g., email address)
🧩 When you require greater pliability in your data model (such as allowing several null values)