Overview
A primary key uniquely identifies each row. A foreign key connects one table to another.
On this page7 sections
The idea
A primary key is a column (or combination of columns) that uniquely identifies every row in a table. In the orders table, order_id is the primary key: no two orders share the same order_id. If you try to insert a duplicate order_id, the database will reject it.
A foreign key is a column that references the primary key of another table. The payments table has an order_id column that points to orders.order_id. This link connects the two tables so you can combine payment information with order information in a single query.
Together, primary keys and foreign keys create the 'relational' part of a relational database. They prevent duplicate records and enable you to join data from multiple tables.
Why this exists
Imagine an e-commerce company where the same order_id appears twice in the orders table. When you calculate total revenue by summing order_total, every duplicated order gets counted twice. Your revenue report shows $200,000 when the real number is $150,000. The CEO makes hiring decisions based on wrong numbers.
Primary keys prevent this by guaranteeing uniqueness. Foreign keys ensure that when you look up payment information for an order, you are connecting to the right record. Without these constraints, data quality breaks down silently and dashboards show incorrect numbers.
Picture this
orders.order_id is the primary key. payments.order_id is the foreign key that references it.
Keys are the foundation of data integrity in relational databases.
| Concept | What it does | Example | What breaks without it |
|---|---|---|---|
| Primary key | Uniquely identifies each row | orders.order_id | Duplicate orders, wrong revenue totals |
| Foreign key | References another table's PK | payments.order_id | Orphan payments with no matching order |
| Composite key | Two+ columns form the key | (order_id, product_id) | Duplicate line items in an order |
You can check if a column is truly unique by grouping on it and looking for counts greater than 1. If the result is empty (zero rows), the column is unique.
A small example
Run the example below in this tab. Read the input, follow the code, then check the output matches what you expect.
-- Check: is order_id unique in orders?
SELECT order_id, COUNT(*) AS copies
FROM orders
GROUP BY order_id
HAVING COUNT(*) > 1;An empty result means no duplicates exist. Every order_id appears exactly once. This is the most basic data quality check a data engineer runs.
Keys enable JOINs
When you learn JOINs in a few lessons, you will match rows on these key columns. Understanding keys now makes joins intuitive later.
Copy-paste without reading the output
Run Sample first. If the numbers or row count look wrong, stop and re-read the previous section before changing code.
Common beginner questions
Can a table have no primary key?
Technically yes, but it is bad practice. Without a primary key, you cannot guarantee uniqueness, and duplicates can silently corrupt your data. Production tables should always have a defined key.
What is a surrogate key?
A surrogate key is an artificial identifier (like an auto-incrementing number) that the database generates. It has no business meaning but guarantees uniqueness. Natural keys (like email or order_id) come from the business data itself. You will learn more about surrogate keys in the dimensional modeling module.
What comes next
Now that you understand what data, databases, tables, and keys are, you are ready to write SQL queries. In the next module, you will learn SELECT, WHERE, GROUP BY, and JOIN: the four operations that power almost every SQL query.
Practice
Run Sample to check if order_id is unique in the orders table. The result should be empty (zero rows), proving every order_id appears exactly once. Then complete Exercise: write your own uniqueness check.