Primary Keys vs Foreign Keys: The Foundation of Database Relationships

Understanding Primary Keys

A primary key is a column — or a set of columns — that uniquely identifies every row in a database table. No two rows can share the same primary-key value, and the value can never be NULL. A table can declare at most one primary key, and choosing one is good practice for application tables.

Natural Keys vs Surrogate Keys

A natural key uses data that already exists in the real-world domain. For example, a country's ISO code (US, FR) or a book's ISBN. Natural keys are meaningful but can change over time and may carry privacy concerns (think Social Security numbers).

A surrogate key is an artificial identifier with no business meaning — typically an auto-incrementing integer or a UUID. Surrogate keys are stable, compact, and easy to index, which is why most modern schemas prefer them.

-- Natural key example
CREATE TABLE countries (
    iso_code  CHAR(2)      PRIMARY KEY,
    name      VARCHAR(100) NOT NULL
);

-- Surrogate key example
CREATE TABLE users (
    id         SERIAL       PRIMARY KEY,
    email      VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMPTZ  DEFAULT now()
);

Understanding Foreign Keys

A foreign key is a column (or group of columns) in one table that references a primary key or another eligible unique key, including a key in the same table. It enforces referential integrity: a non-null foreign-key value must have a matching referenced row. Nullable and composite foreign keys need explicit nullability and matching rules.

CREATE TABLE posts (
    id        SERIAL       PRIMARY KEY,
    title     VARCHAR(200) NOT NULL,
    author_id INT          NOT NULL,
    CONSTRAINT fk_posts_author
        FOREIGN KEY (author_id) REFERENCES users (id)
);

With this constraint in place the database will reject any INSERT into posts where author_id does not match an existing users.id.

How Primary & Foreign Keys Work Together

Primary and foreign keys are the backbone of relational databases. The primary key in one table becomes the anchor; a foreign key in a related table points back to that anchor. This link enforces a declared relationship. You can also JOIN tables and model real-world relationships such as "a user has many posts" or "an order belongs to a customer."

-- Retrieve every post alongside the author's email
SELECT p.title, u.email
FROM   posts p
JOIN   users u ON u.id = p.author_id;

To sketch entities before writing SQL, open the ERD Builder. To inspect the table and foreign-key definitions above, paste them into the SQL-to-ERD editor.

Composite Keys

Sometimes a single column is not enough to guarantee uniqueness. A composite key (also called a compound key) combines two or more columns into a single primary key. This pattern is common in junction tables (also known as join tables or associative tables) used for many-to-many relationships.

-- Junction table for a many-to-many relationship
CREATE TABLE student_courses (
    student_id INT NOT NULL,
    course_id  INT NOT NULL,
    enrolled_at TIMESTAMPTZ DEFAULT now(),
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students (id),
    FOREIGN KEY (course_id)  REFERENCES courses (id)
);

The composite primary key (student_id, course_id) ensures the same student cannot enrol in the same course twice while still allowing each student to take many courses and each course to have many students.

Cascading Actions

When you define a foreign key you can also specify what happens when the referenced row is updated or deleted. These referential actions keep your data consistent without requiring manual cleanup.

• CASCADE — automatically delete or update the child rows when the parent row changes.
• SET NULL — set the foreign-key column to NULL (the column must be nullable).
• SET DEFAULT — reset the column to its default value, which must still satisfy the foreign key. Support varies by engine; MySQL InnoDB rejects this action.
• RESTRICT / NO ACTION — reject a change that leaves invalid references. In PostgreSQL, NO ACTION may be deferred when the constraint is deferrable; RESTRICT is immediate.

CREATE TABLE comments (
    id        SERIAL       PRIMARY KEY,
    body      TEXT         NOT NULL,
    post_id   INT          NOT NULL,
    CONSTRAINT fk_comments_post
        FOREIGN KEY (post_id)
        REFERENCES posts (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

-- Deleting a post now automatically removes all its comments.

Use CASCADE carefully — deleting a parent row will silently wipe every related child row. In audit-heavy systems, consider RESTRICT combined with a soft-delete flag instead.

Best Practices

• Prefer surrogate keys (SERIAL, BIGSERIAL, or UUID) as primary keys for application tables. Use a natural key when the domain identifier is stable enough for your requirements, and plan how changes will affect references.
• Always name your constraints (e.g., fk_posts_author) so that error messages are meaningful.
• Index foreign-key columns. Many databases do not create an index on a foreign key automatically, and missing indexes can severely slow down joins and cascading deletes.
• Think about the cascading action for every foreign key. Defaulting to NO ACTION is safest; switch to CASCADE only when child rows genuinely lose meaning without their parent.
• Choose composite keys from the identity rules of the model. A surrogate key can simplify references, but retain a UNIQUE constraint on the original business key when duplicates must be prevented.
• Model your schema visually first. The ERD Builder lets you sketch a conceptual model and review its SQL conversion — catching design mistakes before they reach production.

References and further reading

• PostgreSQL: constraints, nulls, and referential actions
• MySQL: foreign-key requirements and actions