All posts
SQLNovember 14, 20267 min read

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.

F
Fepiq Team
Fepiq

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 beginners

Quick refresher: tables, rows and columns

A 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.

What is a primary key?

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:

  • It must be unique. No two rows can have the same value.
  • It cannot be NULL. NULL means 'no value', and every row needs an ID.
  • A table can have only one primary key.

Create a table with a primary key

sql
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:

sql
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.

What happens if you repeat a primary key?

sql
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.

Let the database create IDs for you

Typing IDs by hand is boring and risky. Most databases can number rows automatically. In PostgreSQL you can write it like this:

sql
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.

What is a foreign key?

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.

sql
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.

sql
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.

sql
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 vs foreign key

Primary keyForeign key
PurposeIdentifies each rowLinks to a row in another table
Duplicates allowed?NoYes (one customer can have many orders)
NULL allowed?NoYes, unless you forbid it
How many per table?OneAs many as you need

Use the keys together with JOIN

Keys really shine when you combine tables. A JOIN matches rows using the foreign key and the primary key:

sql
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 explained

What happens when you delete a customer?

If 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:

sql
FOREIGN KEY (customer_id) REFERENCES customers(id)
  ON DELETE CASCADE

ON 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.

Tips for choosing keys

  • Name the primary key id. It is simple and common.
  • Name a foreign key after the table it points to, like customer_id.
  • Do not use an email or name as a primary key. People change them.
  • Make the foreign key the same data type as the primary key it references.

Common beginner mistakes

  1. Creating the orders table before the customers table. The referenced table must exist first.
  2. Using different data types for the two linked columns.
  3. Trying to delete a row that other rows still point to.
  4. Forgetting that a primary key cannot be NULL.

Frequently asked questions

What is a primary key in SQL?+

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.

What is the difference between a primary key and a foreign 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.

Can a table have more than one foreign key?+

Yes. A table can have many foreign keys. For example, an order might point to both a customer and a product.

Can a foreign key be NULL?+

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.

Do I need keys to use SQL?+

You can create tables without them, but you should not. Keys keep your data clean, prevent duplicates and make JOINs reliable.

Summary

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 Fepiq

Get new posts in your inbox

Occasional, no-fluff notes on shipping modern software — startups, automation, Laravel, Shopify and more. No spam, unsubscribe anytime.

Keep reading

Related posts

All posts

Let's build something

Ready to ship your next product with Fepiq?

Book a free discovery call. We'll listen, ask sharp questions, and send you a proposal within 3 business days.