SQL PRIMARY KEY Constraint

A PRIMARY KEY is the column (or set of columns) that uniquely identifies every row in a table. It’s the constraint a database uses to guarantee that no two rows represent the same entity, and it gives every other table a stable way to point back at a specific row via a foreign key. Nearly every well-designed table has one, and understanding exactly what it enforces — and what it doesn’t — will save you from some of the most common data-integrity bugs in SQL.

Overview: What a PRIMARY KEY Actually Does

Declaring a column (or columns) as PRIMARY KEY does two things at once: it makes the value unique across every row in the table, and — in standard SQL — it makes the value NOT NULL. Together these guarantee the primary key can always be used to find exactly one row: never zero, never more than one.

Under the hood, the database engine enforces this by building an index over the primary key column(s) — the same kind of B-tree structure used to speed up WHERE lookups. Every time you INSERT or UPDATE a row, the engine walks that index to check whether the new value already exists before it commits the write. That’s why primary-key lookups (WHERE id = 42) are typically the fastest queries you can run against a table — they go straight to a B-tree leaf instead of scanning every row.

A table can have at most one primary key, but that key can span multiple columns — this is called a composite primary key. In that case, uniqueness applies to the combination of columns, not to each column individually.

SQLite has a quirk worth knowing up front: a column declared exactly INTEGER PRIMARY KEY becomes an alias for the table’s internal rowid, giving you free auto-incrementing behavior. Other database systems don’t have this shortcut — MySQL uses AUTO_INCREMENT, PostgreSQL uses SERIAL or GENERATED ... AS IDENTITY, and SQL Server uses IDENTITY.

Syntax

There are two ways to declare a primary key: inline on a single column, or as a separate table-level clause (required for composite keys).

CREATE TABLE table_name (
  column_name INTEGER PRIMARY KEY,
  other_column TEXT
);

CREATE TABLE table_name (
  column_a INTEGER,
  column_b INTEGER,
  PRIMARY KEY (column_a, column_b)
);

Form	When to use it
Column-level: column_name TYPE PRIMARY KEY	A single column uniquely identifies the row (the common case).
Table-level: PRIMARY KEY (col1, col2, ...)	Required whenever the key spans more than one column (a composite key), or when you want to separate the constraint from the column definition.
ALTER TABLE ... ADD PRIMARY KEY	Supported by MySQL, PostgreSQL, and SQL Server to add a primary key after the table already exists. SQLite does not support adding a primary key to an existing table — you must recreate it.

Examples

Example 1: A Simple Single-Column Primary Key

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  grade TEXT
);

INSERT INTO students (id, name, grade) VALUES
  (1, 'Ava', 'A'),
  (2, 'Ben', 'B'),
  (3, 'Cleo', 'A');

SELECT * FROM students ORDER BY id;

Result:

id	name	grade
1	Ava	A
2	Ben	B
3	Cleo	A

id is the primary key, so every value must be unique and non-null. Because it’s declared as INTEGER PRIMARY KEY in SQLite, it also becomes an alias for the row’s internal rowid, which makes lookups by id essentially instant.

Example 2: A Composite Primary Key

CREATE TABLE order_items (
  order_id INTEGER,
  product_id INTEGER,
  quantity INTEGER NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

INSERT INTO order_items (order_id, product_id, quantity) VALUES
  (101, 1, 2),
  (101, 2, 1),
  (102, 1, 5);

SELECT * FROM order_items ORDER BY order_id, product_id;

Result:

order_id	product_id	quantity
101	1	2
101	2	1
102	1	5

No single column here is unique on its own — order 101 appears twice, and product 1 appears twice — but the pair (order_id, product_id) is unique for every row. This is the standard pattern for a junction table linking orders to products, and it also stops the same product from accidentally being added to the same order twice.

Example 3: Letting SQLite Auto-Generate the Key

CREATE TABLE customers_demo (
  customer_id INTEGER PRIMARY KEY AUTOINCREMENT,
  customer_name TEXT NOT NULL
);

INSERT INTO customers_demo (customer_name) VALUES
  ('Nora Chen'),
  ('Wei Zhang'),
  ('Priya Patel');

SELECT * FROM customers_demo;

Result:

customer_id	customer_name
1	Nora Chen
2	Wei Zhang
3	Priya Patel

Because customer_id was never supplied in the INSERT, SQLite generated it automatically, counting up from 1. In MySQL you’d get the same behavior with customer_id INT AUTO_INCREMENT PRIMARY KEY; in PostgreSQL with customer_id SERIAL PRIMARY KEY or GENERATED ALWAYS AS IDENTITY; in SQL Server with customer_id INT IDENTITY(1,1) PRIMARY KEY.

How It Works Under the Hood

When you declare a primary key, the engine silently creates a unique index over those columns — in SQLite this shows up internally as an sqlite_autoindex, in other systems it’s usually named after the constraint. That index is what makes uniqueness checking fast: instead of scanning the whole table on every INSERT, the engine does an index lookup (typically O(log n) on a B-tree) to see whether the incoming value already exists.

Constraint checking happens before the write is committed. If any row in a multi-row INSERT would violate the primary key, the statement is rejected and, by default, rolled back.

SQLite treats INTEGER PRIMARY KEY specially: that column becomes a direct alias for the table’s rowid, the internal 64-bit integer every SQLite row is physically stored under (unless the table is declared WITHOUT ROWID). That’s why inserting NULL into an INTEGER PRIMARY KEY column doesn’t fail — SQLite quietly substitutes the next unused integer, giving auto-increment-like behavior even without the AUTOINCREMENT keyword. AUTOINCREMENT only changes one thing: it guarantees generated numbers are never reused, even after rows are deleted, at the cost of a little extra bookkeeping.

For a composite primary key, the underlying index is built over the columns in the order you listed them, which matters for performance: a lookup that supplies only the first column can still use the index efficiently, but a lookup that supplies only the second column generally cannot.

Common Mistakes

Mistake 1: Inserting a Duplicate Primary Key Value

Trying to insert a row whose primary key already exists raises a constraint violation:

CREATE TABLE accounts (
  account_id INTEGER PRIMARY KEY,
  balance REAL
);

INSERT INTO accounts (account_id, balance) VALUES (1, 500.00);
INSERT INTO accounts (account_id, balance) VALUES (1, 750.00);
-- Error: UNIQUE constraint failed: accounts.account_id

The second INSERT reuses account_id = 1, which the unique index built for the primary key rejects outright. The fix is to use a value that hasn’t been used yet — or better, let the database assign it for you:

CREATE TABLE accounts (
  account_id INTEGER PRIMARY KEY,
  balance REAL
);

INSERT INTO accounts (account_id, balance) VALUES (1, 500.00);
INSERT INTO accounts (account_id, balance) VALUES (2, 750.00);

SELECT * FROM accounts ORDER BY account_id;

Result: two rows, (1, 500.0) and (2, 750.0) — no conflict.

Mistake 2: Assuming a Non-Integer Primary Key Always Rejects NULL

In the SQL standard, PRIMARY KEY implies NOT NULL. SQLite has a long-standing, documented exception: unless the primary key column is exactly INTEGER PRIMARY KEY (or the table is WITHOUT ROWID), SQLite does not automatically reject NULL in a primary key column:

CREATE TABLE codes (
  code TEXT PRIMARY KEY,
  description TEXT
);

INSERT INTO codes (code, description) VALUES ('A100', 'Alpha');
INSERT INTO codes (code, description) VALUES (NULL, 'Mystery');

SELECT * FROM codes;

Result: both rows are accepted, including the one with a NULL code — which is almost never what you actually want from a "unique identifier". Always add NOT NULL explicitly when the primary key isn’t an integer:

CREATE TABLE codes_fixed (
  code TEXT PRIMARY KEY NOT NULL,
  description TEXT
);

INSERT INTO codes_fixed (code, description) VALUES ('A100', 'Alpha');
INSERT INTO codes_fixed (code, description) VALUES (NULL, 'Mystery');
-- Error: NOT NULL constraint failed: codes_fixed.code

With the explicit NOT NULL in place, the bad insert is rejected instead of silently corrupting the table. A safe version that only inserts valid rows:

CREATE TABLE codes_fixed (
  code TEXT PRIMARY KEY NOT NULL,
  description TEXT
);

INSERT INTO codes_fixed (code, description) VALUES
  ('A100', 'Alpha'),
  ('B200', 'Beta');

SELECT * FROM codes_fixed;

Result: two clean rows, ('A100', 'Alpha') and ('B200', 'Beta').

Best Practices

• Give every table a primary key — even a junction/link table should have one (often a composite key over its foreign key columns).
• Prefer small, stable, immutable values for a primary key. A surrogate integer ID that never changes is usually safer than a "natural" key like an email address, which can change over time.
• Don’t rely on SQLite’s non-integer-primary-key NULL loophole — always add NOT NULL explicitly on any primary key column that isn’t INTEGER PRIMARY KEY, and don’t assume the same behavior applies in other databases (MySQL and PostgreSQL both enforce NOT NULL on primary keys regardless of type).
• Use a composite primary key for pure linking tables (e.g. order_items, tag associations) instead of adding a meaningless surrogate ID on top.
• The primary key’s index comes for free — don’t manually add a redundant duplicate index on the same column(s).
• Reference primary keys from other tables with FOREIGN KEY constraints to keep relationships consistent.
• Avoid changing primary key values after rows are created; if other tables reference them via foreign keys, updates can cascade or fail unexpectedly.

Practice Exercises

1. Create a table named books with columns isbn TEXT PRIMARY KEY, title TEXT NOT NULL, and author TEXT. Insert three books with different ISBNs, then write a query that fetches a single book by its isbn.
2. Create a table named enrollments that links students to courses, with a composite primary key over (student_id, course_id) and a grade column. Insert rows showing the same student_id enrolled in two different courses, and confirm the insert succeeds.
3. Using the enrollments table from the previous exercise, try to insert a row that duplicates an existing (student_id, course_id) pair. Predict the error before you run it, then confirm what SQLite reports.

Summary

• A PRIMARY KEY uniquely identifies each row and, per the SQL standard, disallows NULL.
• A table can have only one primary key, but that key can span multiple columns as a composite key.
• The engine enforces the constraint using an automatically created unique index (a B-tree), which also makes primary-key lookups fast.
• In SQLite, a column declared exactly INTEGER PRIMARY KEY becomes an alias for the internal rowid and gets free auto-increment behavior.
• SQLite does not enforce NOT NULL on non-integer primary keys unless you add it explicitly — other databases do this automatically.
• Use AUTOINCREMENT (SQLite), AUTO_INCREMENT (MySQL), or SERIAL/IDENTITY (PostgreSQL/SQL Server) to let the database generate primary key values for you.