Primary Key

A primary key is an attribute (or set of attributes) that uniquely identifies each tuple in a relation. Every table in a relational database must have exactly one primary key. The DBMS automatically enforces uniqueness and creates an Index on the primary key column(s).

Properties

• Unique — No two rows can have the same primary key value
• NOT NULL — Primary key columns cannot contain NULL values
• Immutable — Should rarely change (use a Surrogate Key if the natural key may change)
• Indexed — An Index is automatically created for fast lookups

Natural vs Surrogate Keys

Type	Description	Example
Natural key	A real-world attribute that is inherently unique	email, isbn, ssn
Surrogate Key	System-generated with no business meaning	SERIAL, UUID, AUTO_INCREMENT

Best practice from Database Constraints: use integers for primary keys (efficient joins), never use logical keys (email, username) as they can change.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,          -- Surrogate key
    email VARCHAR(255) UNIQUE       -- Natural key as logical key
);

Composite Primary Key

A composite primary key uses two or more columns together. Common in junction tables that resolve many-to-many relationships:

CREATE TABLE enrollments (
    student_id INT REFERENCES students(id),
    course_id INT REFERENCES courses(id),
    PRIMARY KEY (student_id, course_id)
);