SQL Primary Key and Foreign Key Explained for Beginners
What is a primary key in SQL? Learn primary keys and foreign keys with simple examples, clear code explanations and common mistakes. Perfect for beginners.
What is a primary key in SQL? Learn primary keys and foreign keys with simple examples, clear code explanations and common mistakes. Perfect for beginners.
What is a primary key in SQL, and what is a foreign key? A primary key is a column that gives every row in a table its own unique ID. A foreign key is a column that points to the primary key of another table, so the two tables are linked. This guide explains both in plain words, with small examples you can copy and run.
You do not need any experience. We define every term when it first appears. If you have not made a table yet, read our guide on SQL CREATE TABLE first.
New to tables? Start with creating one, then come back here.
Read: SQL CREATE TABLE for beginnersA database stores data in tables. A table looks like a spreadsheet. Each column is one kind of information (like a name). Each row is one record (like one customer). Keys are special columns that help us manage rows.
A primary key is a column (or set of columns) that identifies each row. Think of a student ID number. Two students can share a name, but never an ID. A primary key follows two rules:
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100)
);Line 1 creates a table named customers. Line 2 makes a column called id that holds whole numbers and marks it as the PRIMARY KEY. Lines 3 and 4 add text columns for the name and email. The last line closes the definition.
Now add two rows:
INSERT INTO customers (id, name, email)
VALUES (1, 'Ava', 'ava@example.com');
INSERT INTO customers (id, name, email)
VALUES (2, 'Ben', 'ben@example.com');Each INSERT adds one row. The first lists the columns, then VALUES gives the data in the same order. Ava gets id 1 and Ben gets id 2.
INSERT INTO customers (id, name, email)
VALUES (1, 'Cara', 'cara@example.com');This fails with an error such as 'duplicate key value violates unique constraint'. The database refuses it because id 1 already belongs to Ava. That protection is the whole point of a primary key.
Typing IDs by hand is boring and risky. Most databases can number rows automatically. In PostgreSQL you can write it like this:
CREATE TABLE customers (
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name VARCHAR(100)
);GENERATED ALWAYS AS IDENTITY tells the database to fill id with 1, 2, 3 and so on. Other systems use different words: MySQL uses AUTO_INCREMENT, and SQL Server uses IDENTITY(1,1). The idea is the same.
A foreign key is a column in one table that stores the primary key value of a row in another table. It creates a link, also called a relationship. For example, an order belongs to a customer. Instead of copying the customer's name into every order, we store the customer's id.
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER,
total DECIMAL(10, 2),
FOREIGN KEY (customer_id) REFERENCES customers(id)
);The orders table has its own primary key, id. The customer_id column holds a customer's id. The line FOREIGN KEY (customer_id) REFERENCES customers(id) says: every value in customer_id must exist in the id column of customers.
INSERT INTO orders (id, customer_id, total)
VALUES (100, 1, 49.99);This order belongs to Ava, because 1 is her id. It works because customer 1 exists.
INSERT INTO orders (id, customer_id, total)
VALUES (101, 99, 15.00);This fails. There is no customer with id 99, so the database blocks the row. Foreign keys stop 'orphan' rows that point to nothing.
| Primary key | Foreign key | |
|---|---|---|
| Purpose | Identifies each row | Links to a row in another table |
| Duplicates allowed? | No | Yes (one customer can have many orders) |
| NULL allowed? | No | Yes, unless you forbid it |
| How many per table? | One | As many as you need |
Keys really shine when you combine tables. A JOIN matches rows using the foreign key and the primary key:
SELECT customers.name, orders.total
FROM orders
JOIN customers ON orders.customer_id = customers.id;SELECT picks the columns we want. FROM orders starts with the orders table. JOIN customers brings in the customers table. ON tells SQL how to match rows: the order's customer_id must equal the customer's id. The result shows each order beside the customer's name.
Want to understand JOIN fully? Learn INNER, LEFT and more.
Read: SQL JOIN explainedIf Ava has orders and you delete her, the foreign key would point to nothing. By default the database stops you. You can choose another behaviour:
FOREIGN KEY (customer_id) REFERENCES customers(id)
ON DELETE CASCADEON DELETE CASCADE means: when a customer is deleted, delete their orders too. Use it carefully, because data disappears automatically. Without it, you must delete the orders first.
A primary key is a column that uniquely identifies each row in a table. Its values must be unique and cannot be NULL. Each table can have only one primary key.
A primary key identifies rows inside its own table. A foreign key is a column that refers to the primary key of another table, which links the two tables together.
Yes. A table can have many foreign keys. For example, an order might point to both a customer and a product.
Yes, by default. NULL means the row is not linked to anything yet. If every row must be linked, add NOT NULL to the column.
You can create tables without them, but you should not. Keys keep your data clean, prevent duplicates and make JOINs reliable.
A primary key gives each row a unique ID. A foreign key uses that ID to connect rows in different tables. Together they keep your data organized and correct. Try the examples above in any SQL database to see the errors for yourself, because that is the fastest way to learn.
Want help learning or building with databases? Talk to the Fepiq team.
Contact FepiqOccasional, no-fluff notes on shipping modern software — startups, automation, Laravel, Shopify and more. No spam, unsubscribe anytime.
Keep reading
Learn SQL AND, OR and NOT operators with simple examples. See how to combine conditions in WHERE and avoid the common mistakes beginners make.
Learn JavaScript template literals with simple examples: backticks, ${} variables, and multi-line strings. A beginner-friendly guide you can copy and run.
Let's build something
Book a free discovery call. We'll listen, ask sharp questions, and send you a proposal within 3 business days.