KAHIBARO
Discord Login Register ☰
☰

9.4. Primary Keys

Table of Contents

Why Primary Keys Matter

In every relational database table, you need a reliable way to talk about one specific row. That is what a primary key is for.

A primary key is a column, or a combination of columns, that uniquely identifies each row in a table. Without a primary key, it is hard to update, delete, or relate data correctly.

Primary key rule: Every table should have a primary key, and primary key values must be unique and non‑NULL.

Examples of things that naturally have unique identifiers:

EntityReal world unique identifierPossible primary key column
UserEmail, username, internal IDid or email
ProductBarcode, SKU, internal product IDid or sku
OrderOrder numberid
CountryISO code (e.g. "US", "FR")code

In practice, you will very often use an integer column named id as the primary key.

Properties of a Primary Key

A primary key has a few strict properties.

Uniqueness

Each primary key value must be unique within the table.

If id is the primary key in a users table, and you have these rows:

idemail
1alice@example.com
2bob@example.com

Then you cannot insert another row with id = 1 or id = 2.

In SQL, the database enforces this:

sql 
CREATE TABLE users (
    id   INT PRIMARY KEY,
    email TEXT
);
-- OK
INSERT INTO users (id, email)
VALUES (1, 'alice@example.com');
-- Error: duplicate key value violates unique constraint
INSERT INTO users (id, email)
VALUES (1, 'another@example.com');

The primary key automatically has a unique constraint.

Non‑NULL

Primary key columns cannot contain NULL. NULL means "unknown" or "missing". A key that is unknown cannot uniquely identify anything.

sql 
CREATE TABLE products (
    id   INT PRIMARY KEY,
    name TEXT
);
-- Error: null value in column "id" violates not-null constraint
INSERT INTO products (id, name)
VALUES (NULL, 'Some Product');

If you try to insert NULL in a primary key column, the database will reject it.

Stability

Primary key values should rarely change. If you change them, you can break relationships with other tables that refer to that key.

For example, if users.id is used in an orders table as a foreign key, and you change users.id from 5 to 10, every row in orders that refers to user 5 might break.

So, as a rule, prefer keys that:

Good practice: Choose primary keys that are stable, simple, and never reused for a different row.

Single‑Column vs Composite Primary Keys

A primary key can use one column, or multiple columns together.

Single‑Column Primary Keys

This is the most common pattern. One column, often named id, is declared as the primary key.

sql 
CREATE TABLE users (
    id    SERIAL PRIMARY KEY,
    email TEXT NOT NULL
);

Explanation:

Benefits:

You will see this pattern almost everywhere.

Composite Primary Keys

A composite primary key uses multiple columns together to uniquely identify a row.

Example: A table that stores a student's grade for a course.

sql 
CREATE TABLE course_grades (
    student_id INT NOT NULL,
    course_id  INT NOT NULL,
    grade      INT,
    PRIMARY KEY (student_id, course_id)
);

Here, the primary key is (student_id, course_id):

Table example:

student_idcourse_idgrade
110190
110285
210188

You cannot insert another row with (student_id = 1, course_id = 101).

Composite keys are useful when:

However, composite keys can make joins and foreign keys more complex, because every related table must include all columns of the composite key.

Natural Keys vs Surrogate Keys

There are two main strategies for choosing what column to use as your primary key.

Natural Keys

A natural key is a primary key that comes from real world data and already uniquely identifies an entity.

Examples:

TableNatural key candidate
countriescode (e.g. "US", "FR")
usersemail
productssku

Example using email as primary key:

sql 
CREATE TABLE users (
    email TEXT PRIMARY KEY,
    name  TEXT NOT NULL
);

Pros:

Cons:

Because natural keys can change, using them as primary keys can create maintenance problems later.

Surrogate Keys

A surrogate key is a key that has no meaning in the real world. It only exists to identify rows inside the database.

Common surrogate keys:

Example:

sql 
CREATE TABLE users (
    id    SERIAL PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    name  TEXT NOT NULL
);

Here:

Pros:

Cons:

In most backend applications, especially larger ones, you will use surrogate keys for primary keys and add unique constraints on natural identifiers when needed.

Very common pattern: Use a surrogate primary key (like id SERIAL), plus UNIQUE constraints on any real world identifiers you care about, such as email or username.

Creating Primary Keys in SQL

Different SQL dialects have slightly different syntax, but the ideas are the same. Here are common patterns you will use.

Defining a Single‑Column Primary Key

You can define the primary key directly on a column.

sql 
CREATE TABLE users (
    id    SERIAL PRIMARY KEY,
    email TEXT NOT NULL
);

Here, PRIMARY KEY does two things:

Alternatively, you can define it as a separate constraint:

sql 
CREATE TABLE users (
    id    INT NOT NULL,
    email TEXT NOT NULL,
    PRIMARY KEY (id)
);

Both definitions mean the same: id is the primary key.

Example Insert and Select

sql 
INSERT INTO users (email)
VALUES ('alice@example.com'),
       ('bob@example.com');
SELECT * FROM users;

If id is SERIAL, the database will generate values like 1, 2, 3 automatically.

Defining a Composite Primary Key

To define a composite key, list multiple columns.

sql 
CREATE TABLE enrollment (
    student_id INT NOT NULL,
    course_id  INT NOT NULL,
    enrolled_at TIMESTAMP NOT NULL,
    PRIMARY KEY (student_id, course_id)
);

Attempting to insert a duplicate pair:

sql 
INSERT INTO enrollment (student_id, course_id, enrolled_at)
VALUES (1, 101, NOW());
-- This will fail, because (1, 101) already exists
INSERT INTO enrollment (student_id, course_id, enrolled_at)
VALUES (1, 101, NOW());

The database will raise an error about duplicate key value.

Auto‑Incrementing Primary Keys

Very often, you want the database to automatically generate a new integer ID for each row. Different databases have different keywords, but the concept is the same.

Typical Auto‑Increment Syntax

Examples:

DatabaseExample syntax
PostgreSQLid SERIAL PRIMARY KEY
MySQLid INT AUTO_INCREMENT PRIMARY KEY
SQLiteid INTEGER PRIMARY KEY AUTOINCREMENT

Example in PostgreSQL style:

sql 
CREATE TABLE posts (
    id      SERIAL PRIMARY KEY,
    title   TEXT NOT NULL,
    content TEXT NOT NULL
);
INSERT INTO posts (title, content)
VALUES ('First post', 'Hello world!');

Resulting table:

idtitlecontent
1First postHello world!

You did not provide id. The database filled it for you.

This is often called a surrogate key with auto increment.

Why Auto‑Increment Is Popular

Potential issue: In distributed systems or when multiple databases are merged, plain auto increment IDs can clash across databases. In such cases, UUIDs or other strategies may be used, but that goes beyond basic primary keys.

Primary Keys and Indexes

When you create a primary key, the database automatically creates an index on that key.

An index is a structure that makes lookups by the key faster.

Example:

sql 
CREATE TABLE customers (
    id   SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

The database creates an index on id. When you run:

sql 
SELECT * FROM customers WHERE id = 123;

The query can use the index to find the row quickly.

You can think of the primary key index as the table's "main index" used by the storage engine.

You do not need to manually add an index on the primary key column. It is already there.

Working With Primary Keys in Queries

Primary keys are used very often in everyday queries.

Selecting by Primary Key

sql 
SELECT * FROM users WHERE id = 5;

This is the typical way you fetch one record in an API endpoint like:

http 
GET /users/5

Your backend will translate that 5 into a query like the one above.

Updating by Primary Key

sql 
UPDATE users
SET email = 'new-email@example.com'
WHERE id = 5;

Because id is unique, this query affects at most one row.

If you mistakenly write a condition that matches multiple rows, you can accidentally update too much data. Using the primary key in the WHERE clause helps avoid that.

Deleting by Primary Key

sql 
DELETE FROM users
WHERE id = 5;

Again, id ensures you only delete the intended row.

Primary Keys and Relationships

Primary keys are closely connected to foreign keys and relationships, which are covered in another chapter. Here is the basic idea.

Example of a simple relationship:

sql 
CREATE TABLE users (
    id    SERIAL PRIMARY KEY,
    email TEXT NOT NULL UNIQUE
);
CREATE TABLE orders (
    id      SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    total   NUMERIC(10, 2) NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

Here:

You can then join tables using the primary key:

sql 
SELECT o.id AS order_id, u.email, o.total
FROM orders o
JOIN users u ON o.user_id = u.id;

Choosing a Good Primary Key

When you design tables, you need to decide what the primary key will be. Use these guidelines.

Prefer Surrogate Keys for Most Application Tables

For typical backend systems, a pattern like this is common:

sql 
CREATE TABLE users (
    id        SERIAL PRIMARY KEY,
    email     TEXT NOT NULL UNIQUE,
    username  TEXT NOT NULL UNIQUE,
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

Reasons:

Use Natural Keys When They Are Truly Stable

For small reference tables where codes never change, natural keys can be fine.

Example:

sql 
CREATE TABLE countries (
    code CHAR(2) PRIMARY KEY,  -- ISO code, e.g. 'US', 'FR'
    name TEXT NOT NULL
);

Here, code is well defined and stable. Adding an extra id might not be necessary.

Another example:

sql 
CREATE TABLE languages (
    code CHAR(2) PRIMARY KEY,
    name TEXT NOT NULL
);

Use Composite Keys for Many‑to‑Many Join Tables

Join tables that connect two entities often use composite keys.

Example: Users can subscribe to many newsletters, and each newsletter has many users.

sql 
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email TEXT NOT NULL UNIQUE
);
CREATE TABLE newsletters (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);
CREATE TABLE user_newsletters (
    user_id       INT NOT NULL,
    newsletter_id INT NOT NULL,
    subscribed_at TIMESTAMP NOT NULL DEFAULT NOW(),
    PRIMARY KEY (user_id, newsletter_id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (newsletter_id) REFERENCES newsletters(id)
);

The composite primary key (user_id, newsletter_id):

Some teams still add a surrogate id column even here, but for many join tables, a composite primary key is perfectly fine and very clear.

Common Mistakes With Primary Keys

Understanding common mistakes helps you avoid problems later.

Not Having a Primary Key

Some people skip primary keys to "keep it simple". This makes it hard to:

Avoid tables without primary keys in real applications.

Using Unstable Data as a Primary Key

Using values that can change frequently as the primary key causes trouble.

Bad examples:

If you must allow changes, this can break foreign keys or require complex cascading updates.

Better approach:

sql 
CREATE TABLE users (
    id           SERIAL PRIMARY KEY,
    phone_number TEXT NOT NULL UNIQUE
);

Reusing Primary Key Values

You should never "recycle" a primary key value for a different row.

Bad pattern:

  1. Delete row with id = 10.
  2. Insert a new and completely different row with id = 10.

This confuses logs, references, and historical data. Let the database assign new IDs and keep them unique over time.

Overcomplicating Keys

Sometimes developers try to encode too much meaning into the primary key.

Example:

sql 
-- Trying to encode year and type into the key
order_id VARCHAR(50) PRIMARY KEY  -- '2024-ONLINE-00001'

This makes:

A simpler approach:

sql 
CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    order_code  TEXT NOT NULL UNIQUE  -- like '2024-ONLINE-00001'
);

Summary

Views: 7

Comments

Please login to add a comment.

Don't have an account? Register now!

© 2025 Kahibaro
Visitors: 3540 / 24h
Impressum / Imprint
Datenschutz / Data Policy