A primary key uniquely identifies each row in a table, usually an auto-incrementing id.
A foreign key is a column that refers to the primary key of another table. It creates a relationship and prevents "orphan" rows, such as an order for a customer who doesn't exist.
Options such as ON DELETE CASCADE decide what happens to child rows when the parent is deleted.
SQLite only enforces foreign keys after PRAGMA foreign_keys = ON;. MySQL (InnoDB) and PostgreSQL enforce them by default.
💡 Key Takeaways: SQL Primary & Foreign Keys
Mastering SQL Primary & Foreign Keys helps you write clean and reliable SQL code. Follow standard conventions, test edge cases, and keep logic simple. Try running the code example below and complete the exercises to build confidence.
SQL Primary & Foreign Keys Code Example
SQL
PRAGMA foreign_keys = ON;
CREATE TABLE Authors (AuthorID INTEGER PRIMARY KEY, Name TEXT NOT NULL);
CREATE TABLE Books (
BookID INTEGER PRIMARY KEY,
Title TEXT NOT NULL,
AuthorID INTEGER REFERENCES Authors(AuthorID) ON DELETE CASCADE
);
INSERT INTO Authors VALUES (1, 'Bano Qudsia');
INSERT INTO Books (Title, AuthorID) VALUES ('Raja Gidh', 1);
SELECT b.Title, a.Name FROM Books b JOIN Authors a USING (AuthorID);
-- Fails: author 42 does not exist
INSERT INTO Books (Title, AuthorID) VALUES ('Unknown Book', 42);
Free sign-in required · Google login available
SQL Primary & Foreign Keys Practice Exercises
Check what you learned in “SQL Primary & Foreign Keys”.
/ 3 solved
Fill in the blank
Link the column to the Customers table.
CustomerID INTEGER Customers(CustomerID)
✓ Correct!Not quite. Try again.
Multiple choice
What does a foreign key prevent?
✓ Correct!Not quite. Try again.
Code challenge
Select the OrderID of orders whose CustomerID does not exist in Customers (the 'orphan' orders).
Write the code in the live editor. Your answer is checked automatically.