What is the difference between a primary key and a foreign key?

The primary key defines row identity. The foreign key defines a relationship and protects referential integrity: you cannot insert a child row that points to a missing parent, and you cannot delete a parent that still has children unless the constraint says otherwise.

CREATE TABLE customers (
  id BIGINT PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers(id),
  total_cents INTEGER NOT NULL CHECK (total_cents >= 0),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Behavior and trade-offs

• A primary key is unique, NOT NULL, and usually backed by a unique index
• A table can have only one primary key, but many foreign keys
• Foreign keys are not automatically indexed in every database, so index the referencing column
• ON DELETE CASCADE removes children, while RESTRICT or NO ACTION blocks the delete
• Foreign keys add write-time checks but prevent orphaned data

On large write-heavy systems, some teams drop foreign keys and enforce relationships in the application. That can reduce lock contention, but it trades away a strong database guarantee, so the decision needs monitoring and cleanup jobs.