PostgreSQL Primary Keys and Foreign Keys for Beginners

A primary key is a column (or group of columns) that uniquely identifies each row in a PostgreSQL table, and a foreign key is a column that points to a primary key in another table to link the two together. If that sentence felt fast, don't worry - this guide breaks both ideas down from scratch, with small examples you can copy, paste, and run yourself.

By the end of this tutorial you will know how to add a primary key to a table, how to connect two tables with a foreign key, and why PostgreSQL uses these rules to keep your data clean and consistent.

What Is a Primary Key?

A primary key is a value that is unique for every row in a table. No two rows can share the same primary key, and a primary key column can never be empty (NULL). Think of it like a passport number: every person gets one, and no two people share the same one.

Most beginner tables use a simple auto-incrementing number as the primary key. In PostgreSQL, the easiest way to create one is with the SERIAL type, which automatically generates 1, 2, 3, and so on for each new row.

CREATE TABLE customers (
  customer_id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE
);

Line by line: CREATE TABLE customers starts a new table named customers. customer_id SERIAL PRIMARY KEY creates a column that auto-numbers itself and is marked as the primary key. name TEXT NOT NULL stores each customer's name and refuses to let it be empty. email TEXT UNIQUE stores an email address and makes sure no two customers share the same one - UNIQUE is similar to a primary key but a table can have many UNIQUE columns, while it can only have one primary key.

What Is a Foreign Key?

A foreign key is a column in one table that stores the primary key value from another table, creating a link between the two. The table with the foreign key is often called the "child" table, and the table it points to is the "parent" table.

For example, imagine you also have an orders table. Each order belongs to one customer, so the orders table needs a way to say "this order belongs to that customer." That's exactly what a foreign key does.

CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(customer_id),
  order_date DATE,
  total NUMERIC(10,2)
);

order_id SERIAL PRIMARY KEY gives every order its own unique ID, just like before. customer_id INTEGER REFERENCES customers(customer_id) is the foreign key - it stores a number that must already exist in the customer_id column of the customers table. order_date DATE stores when the order was placed. total NUMERIC(10,2) stores a price with up to 10 digits total and 2 digits after the decimal point, which is perfect for money.

Primary Key vs Foreign Key: What's the Difference?

Why Do Primary and Foreign Keys Matter?

It's tempting to skip keys when you're just starting out, but they solve real problems that show up quickly once your app has real users.

• Data integrity: PostgreSQL blocks you from inserting an order for a customer_id that doesn't exist, so you can never have "orphan" data pointing at nothing.
• Faster lookups: primary keys are automatically indexed, so finding one row among millions is fast.
• Clear relationships: anyone reading your table structure can immediately see how tables connect to each other.
• Safer updates and deletes: PostgreSQL can warn you or stop you from deleting a row that other rows still depend on.

Seeing the Rules in Action

Let's insert a customer, then a valid order, then try an invalid order to see PostgreSQL protect your data.

INSERT INTO customers (name, email)
VALUES ('Ana Silva', 'ana@example.com');

INSERT INTO orders (customer_id, order_date, total)
VALUES (1, '2026-09-01', 49.99);

The first statement adds one customer, who automatically gets customer_id = 1 from the SERIAL column. The second statement adds an order that points to customer_id 1, which exists, so PostgreSQL accepts it.

INSERT INTO orders (customer_id, order_date, total)
VALUES (999, '2026-09-01', 49.99);

-- ERROR: insert or update on table "orders" violates
-- foreign key constraint "orders_customer_id_fkey"
-- DETAIL: Key (customer_id)=(999) is not present in table "customers".

This time we tried to attach the order to customer_id 999, but no such customer exists. PostgreSQL refuses the insert and shows an error instead of silently saving broken data. This is the foreign key doing its job.

Joining Tables Using the Foreign Key

The whole reason to link tables is so you can pull related data back together with a JOIN. If you're new to JOIN, our SQL JOIN guide covers the basics in more depth - here's a quick preview using the tables above.

SELECT orders.order_id, customers.name, orders.total
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id;

This query starts from the orders table, joins it to customers by matching customer_id in both tables, and returns the order ID, the customer's name, and the order total in one combined result - even though the name lives in a completely different table.

Primary Keys Made of More Than One Column

Sometimes no single column is unique on its own, but a combination of two columns is. This is called a composite primary key, and it's common for linking tables that connect two other tables together.

CREATE TABLE enrollments (
  student_id INTEGER REFERENCES students(student_id),
  course_id INTEGER REFERENCES courses(course_id),
  enrolled_on DATE,
  PRIMARY KEY (student_id, course_id)
);

Here, student_id and course_id are each foreign keys pointing to other tables, but together they also form the primary key of this table. This guarantees the same student can't be enrolled in the same course twice, while still allowing one student to take many courses and one course to have many students.

Common Mistakes Beginners Make

• Forgetting PRIMARY KEY on the ID column, which allows duplicate or empty IDs to sneak in.
• Using a name or email as the primary key instead of a stable ID - names change, IDs shouldn't.
• Trying to delete a parent row (like a customer) while child rows (like orders) still reference it, then being surprised by an error.
• Not matching data types between the primary key and the foreign key column - both should be the same type, such as INTEGER.