SQL Primary Key Explained

Every table that matters needs a reliable way to identify one specific row out of potentially millions. That's the job of a primary key — a column (or set of columns) that uniquely identifies each row in a table. No two rows can share the same primary key value, and a primary key column can never be NULL.

Primary keys aren't just a good habit — they're what makes foreign keys and reliable joins between tables possible in the first place. Without a dependable unique identifier, there's no safe way for one table to reference a specific row in another.

Defining a Primary Key Inline

The simplest and most common approach is to mark a column as the primary key right where it's declared in CREATE TABLE:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    hire_date DATE
);

Here, id is guaranteed unique and non-null for every row. Most of the time you'll combine this with AUTO_INCREMENT (or its database-specific equivalent) so the database generates the next id automatically instead of you having to track it yourself.

Defining a Primary Key as a Table Constraint

You can also declare the primary key separately, as a named constraint at the end of the column list. This form is required for composite primary keys (more on that below), and some teams prefer it even for a single column because it lets you name the constraint explicitly:

CREATE TABLE employees (
    id INT,
    name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    hire_date DATE,
    CONSTRAINT pk_employees PRIMARY KEY (id)
);

Both forms produce the same result — a guarantee that id is unique and not null — just written differently.

What Happens If You Violate It

Try to insert a row with a primary key value that already exists, and the database rejects the whole statement:

INSERT INTO employees (id, name, department, salary, hire_date)
VALUES (1, 'Duplicate Dan', 'Sales', 50000, '2024-01-01');

Result:

ERROR: duplicate key value violates unique constraint "pk_employees"

This is exactly the safety net you want — it's the database actively preventing data corruption, rather than silently allowing two "different" rows that are actually supposed to be the same record.

Composite Primary Keys

Sometimes no single column is unique on its own, but a combination of columns is. A classic example is a table tracking which employees work on which projects — neither employee_id nor project_id alone is unique, but the pair together is:

CREATE TABLE employee_projects (
    employee_id INT,
    project_id INT,
    assigned_date DATE,
    PRIMARY KEY (employee_id, project_id)
);

This guarantees a given employee can only appear once per project, while still letting the same employee appear on many different projects (and the same project have many different employees).

Whichever form you use, a well-chosen primary key is the foundation everything else in a well-designed table builds on — it's worth getting right before you start adding data.

---