PRIMARY KEY Constraint

What is a PRIMARY KEY?

A PRIMARY KEY uniquely identifies each row in a table. It enforces two rules: the column(s) must be unique and must not contain NULL values.

Every table should have a primary key. It is the foundation for referential integrity and allows other tables to reference rows via foreign keys.

Syntax

Column-level primary key:

CREATE TABLE students (
  id SERIAL PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50)
);

Table-level (composite) primary key:

CREATE TABLE enrollments (
  student_id INT,
  course_id INT,
  PRIMARY KEY (student_id, course_id)
);

When to Use

• Every table should have a primary key for data integrity
• Use a single-column key for most tables (often a SERIAL or UUID)
• Use a composite key for junction/bridge tables

Key Points

1. Uniqueness — No two rows can share the same primary key value.
2. NOT NULL — Primary key columns automatically cannot be NULL.
3. Auto Index — PostgreSQL automatically creates a unique B-tree index on the primary key columns.
4. One Per Table — A table can have only one primary key, but it can span multiple columns (composite key).
5. SERIAL — Commonly paired with SERIAL or BIGSERIAL for auto-incrementing integer IDs.