Primary Key vs Foreign Key in SQL
What primary and foreign keys are, how they differ, and how a runnable customers-and-orders example shows the database enforcing them.
Short answer: a primary key uniquely identifies each row in its own table, and a foreign key is a column that points to the primary key of another table to link the two. Primary key vs foreign key in SQL comes down to this: one names the row, the other connects rows across tables. Together they keep related data consistent.
What Is a Primary Key?
A primary key is a column (or a set of columns) whose value is different for every row. It must be unique and cannot be NULL. Each table can have only one primary key. A customer's id is a typical example: two customers can share a name, but never an id.
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. In an orders table, a customer_id column says which customer placed the order. The foreign key can repeat, because one customer can have many orders, and it may be NULL if the relationship is optional. A table can have several foreign keys.
What Is the Difference Between Primary Key and Foreign Key?
Point | Primary key | Foreign key |
|---|---|---|
Purpose | Identify a row in its own table | Link a row to a row in another table |
Duplicates | Not allowed | Allowed |
NULL | Not allowed | Allowed unless you forbid it |
How many per table | One (it may span several columns) | Zero, one or many |
Example: Customers and Orders
This runs with Python's built-in sqlite3. SQLite checks foreign keys only after you turn the feature on:
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("PRAGMA foreign_keys = ON")
db.executescript('''
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
total REAL,
FOREIGN KEY (customer_id) REFERENCES customers(id)
);
INSERT INTO customers (id, name) VALUES (1, 'Asha'), (2, 'Ravi');
INSERT INTO orders (id, customer_id, total) VALUES (10, 1, 25.0), (11, 1, 40.0), (12, 2, 15.0);
''')
query = "SELECT c.name, o.id, o.total FROM orders o JOIN customers c ON c.id = o.customer_id ORDER BY o.id"
for row in db.execute(query):
print(row)
# ('Asha', 10, 25.0)
# ('Asha', 11, 40.0)
# ('Ravi', 12, 15.0)
The JOIN follows the foreign key to bring names and orders together. See INNER JOIN and table aliases for how that works.
What Does the Database Protect You From?
Add these lines to the script above to see the rules in action:
for stmt in ["INSERT INTO customers (id, name) VALUES (1, 'Dup')",
"INSERT INTO orders (id, customer_id, total) VALUES (13, 99, 5.0)",
"DELETE FROM customers WHERE id = 1"]:
try:
db.execute(stmt)
except sqlite3.IntegrityError as e:
print("Blocked:", e)
All three are rejected: a duplicate primary key, an order for a customer that does not exist, and deleting a customer who still has orders. This is called referential integrity: no order can point to a missing customer.
Common Mistakes and Interview Tips
No primary key at all. Without one, you cannot reliably update or delete a single row.
Using a changing value as the key. An email can change, so many designs use a separate numeric id.
Assuming a foreign key must be unique. It usually repeats; that is how one-to-many works.
Interview tip: mention that a foreign key may reference a unique column, that it can be
NULL, and that you can choose what happens on delete (block it, or cascade it).
To go deeper, read Tables, keys and relationships, or compare relational design with other approaches in SQL vs NoSQL.
Key Takeaway
A primary key names each row; a foreign key points to a primary key elsewhere. Use both and the database itself will stop broken links between your tables.
Ready to start? You will practise keys, joins and clean queries on a real practice database. Begin with the free course SQL and Data Handling for AI: Beginner.
Related articles
More in SQL and Data
SQL vs Python for Data Analysis
SQL and Python do different jobs in data analysis. See what each is best at, a side-by-side example, and which one to learn first.
4 min readNov 15, 2026WHERE vs GROUP BY vs HAVING in SQL
WHERE filters rows, GROUP BY builds groups and HAVING filters groups. See one worked example, the order SQL runs them in, and the mistakes to avoid.
3 min readNov 10, 202625 SQL Interview Questions for Freshers
The 25 SQL questions freshers meet most often, with short answers on keys, joins, NULL, indexes and classic query puzzles.
5 min readNov 7, 2026MySQL vs PostgreSQL vs SQLite: Which Should You Learn?
SQL is the language; MySQL, PostgreSQL and SQLite are database systems. See how they differ and which one to learn first.
3 min readNov 5, 2026SQL vs NoSQL: Differences and When to Use Each
SQL stores data in structured tables, NoSQL in flexible documents, key-values or graphs. Here are the differences, a comparison table and a simple guide to choosing.
4 min readNov 1, 2026What Is SQL and What Is It Used For?
SQL is the standard language for working with relational databases. Learn what it is, the four core statements, and why AI engineers need it.
3 min readOct 30, 2026
Comments
Sign in to join the discussion.

