Primary Key
SQL · Working with Tables
Theory
A PRIMARY KEY is the column, or set of columns, that uniquely identifies each row. The database enforces two rules on it: values must be unique and they can never be NULL. An employee id of 1 can exist only once, and every employee must have an id. A table can have only one primary key, but it may be composite, such as (order_id, line_no) for order lines.
Most engines automatically build an index on the primary key, so lookups and joins by key are fast. Other tables point to it with foreign keys. Keys can be natural (a real-world value like PAN number) or surrogate (a meaningless number from an identity column); surrogate keys are popular because they never change. Note that SQLite, for historical reasons, allows NULL in non-integer primary keys unless you also add NOT NULL.
Why we use it
Without a reliable identifier you cannot safely update, delete or join a specific row, and duplicates creep in. The primary key guarantees that every row can be found and referenced exactly.
When to use it
Give almost every table a primary key. Use a surrogate identity key when no natural value is stable and unique; use a composite key for link tables. For other columns that must be unique, such as email, add a UNIQUE constraint.
How it works
- INSERT or UPDATE a row
- Is the key value NULL? Yes: Reject the row. No: Check uniqueness.
- Does another row already have this key? Yes: Reject: duplicate key. No: Accept the row.
- Update the primary key index
- Row is stored and can be referenced
Syntax
column datatype PRIMARY KEYExample
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100)
);Key points
- Values must be unique and non-null
- A table can have only one primary key, single or composite
- Usually indexed automatically, making key lookups fast
- Other tables reference it with foreign keys
- Surrogate (generated) keys are stable; natural keys can change