Primary Key vs Foreign Key: Relationships in Databases

The short answer

• A primary key (PK) is a column, or set of columns, whose value uniquely identifies a row in its table. It is unique, never NULL, and a table has only one.
• A foreign key (FK) is a column in one table that references the primary key (or another unique column) of another table. It says "this order belongs to that customer".

The database enforces both. You cannot insert two customers with the same ID, and you cannot create an order for a customer that does not exist. This is called referential integrity.

CREATE TABLE customers (
  id   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE orders (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers (id),
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

Natural and surrogate keys

• A natural key comes from the real world: a passport number, an email, a tax ID.
• A surrogate key is an artificial ID generated by the database or application.

Surrogate keys are the usual choice for primary keys because real-world values change: people change emails, documents get reissued. Keep natural identifiers as ordinary columns with a UNIQUE constraint.

Relationship types

One-to-many

The most common type. One customer has many orders; each order has one customer. The FK goes on the "many" side: orders.customer_id.

One-to-one

One user has one profile. Put an FK in the profile table and make it unique, often by making it the primary key at the same time:

CREATE TABLE profiles (
  user_id BIGINT PRIMARY KEY REFERENCES users (id),
  bio     TEXT
);

One-to-one is useful for splitting rarely used or sensitive columns into a separate table. If the data is always read together, a single table is usually simpler.

Many-to-many

A student attends many courses, and a course has many students. Neither table can hold the FK alone, so you add a junction table (also called a link or bridge table):

CREATE TABLE enrollments (
  student_id  BIGINT REFERENCES students (id),
  course_id   BIGINT REFERENCES courses (id),
  enrolled_at DATE NOT NULL,
  PRIMARY KEY (student_id, course_id)
);

The composite primary key prevents the same student from enrolling in the same course twice. The junction table can also store facts about the link itself, such as the enrollment date.

What happens on delete

When you delete a customer who still has orders, the FK decides what to do:

customer_id BIGINT REFERENCES customers (id) ON DELETE CASCADE

Use CASCADE for data that has no meaning without its parent, such as order items without an order. Avoid it for valuable records: cascading from customers to orders to payments can quietly wipe out financial history. For such data, prefer RESTRICT or a soft delete (an is_deleted or deleted_at column).

Serial IDs vs UUIDs

Practical guidance:

• For most internal tables, identity/serial keys are simple and efficient.
• Choose UUIDs when IDs are created on clients or in several services, when you merge data from different sources, or when you do not want to expose record counts.
• If you use UUIDs as a primary key, consider time-ordered variants such as UUIDv7, which keep inserts closer together in the index.
• Never rely on unguessable IDs as security; always check access rights.

Common mistakes

• Forgetting to index FK columns. PostgreSQL does not create an index on the referencing column automatically, while MySQL InnoDB does. Joins and cascading deletes on large tables then become slow.
• Mismatched types, such as INT in one table and BIGINT in the other.
• Skipping FKs "for speed" and ending up with orphan rows that point nowhere.
• Using a mutable value, like an email, as the primary key.