Primary and Foreign Keys in SQL Tables (Explained With a Join)
A primary key names each row once; a foreign key in another table points back at it. See both on a sample online shop database, follow them with a join, and find an order whose key points at nothing.
Step by step
Why keys
- How tables connect. Every join depends on 2 kinds of columns: primary keys and foreign keys. If you get them right, your numbers add up. Here they are in a sample online shop database with a customers table and an orders table.
Primary key
- Primary key. Start with the customers table. Every customer has a customer_id column. Select customer_id, first_name and last_name from customers. It returns 40 customers with 40 different IDs. customer_id is the primary key. It names each row once and is never empty or repeated.
SELECT customer_id, first_name, last_name FROM customers; - Check it is unique. To check that no ID repeats, group customers by customer_id. Then use HAVING COUNT(*) > 1 to keep only the IDs that appear more than once. The query returns no rows. No ID is used twice, so each customer can be found by one number.
SELECT customer_id, COUNT(*) AS copies FROM customers GROUP BY customer_id HAVING COUNT(*) > 1;
Foreign key
- Foreign key. Now the orders table. Its own primary key is order_id, but it also has a customer_id column. Select order_id, customer_id and order_total from orders, filtered with WHERE customer_id = 9. It returns 9 orders, all for customer 9. Here customer_id is a foreign key. It points at one row in customers, and one customer can have many orders.
SELECT order_id, customer_id, order_total FROM orders WHERE customer_id = 9;
Join on the keys
- Join on the keys. A join follows that pointer. Join orders (o) to customers (c). The ON line says the primary key c.customer_id equals the foreign key o.customer_id. The same 9 orders come back, now with the customer's first name, Chloe. Each order found its one customer through the keys.
SELECT o.order_id, c.first_name FROM orders AS o JOIN customers AS c ON c.customer_id = o.customer_id WHERE o.customer_id = 9;
Broken keys
- Find orphans. To find broken keys, use a LEFT JOIN from orders to customers, which keeps every order. Then WHERE c.customer_id IS NULL keeps only the orders whose foreign key matches no customer. It returns 1 row: order 1141 points at customer 99, who doesn't exist. This is called an orphan. An inner join would quietly drop it from your totals.
SELECT o.order_id, o.customer_id FROM orders AS o LEFT JOIN customers AS c ON c.customer_id = o.customer_id WHERE c.customer_id IS NULL;
Recap
- The key rules. A primary key names each row once and is never repeated. A foreign key in another table points back at it, so one customer can have many orders. Join on the two keys, and use a LEFT JOIN with IS NULL to find orphans.
Common mistakes
- Using an inner join for totals when orphans exist. An inner join drops rows like order 1141, whose customer_id matches no customer, so totals come out too low with no warning. Run the LEFT JOIN ... IS NULL check first to see what would be left out.
- Checking the wrong column for NULL. WHERE o.customer_id IS NULL only finds orders with an empty customer ID. To find orders that point at a missing customer, test the column from the joined table: WHERE c.customer_id IS NULL.
- Joining on the wrong pair of columns. Writing ON c.customer_id = o.order_id still runs, but it matches the wrong rows. Always join the primary key to the foreign key that points at it: c.customer_id = o.customer_id.
- Assuming the database blocks broken keys. A database only stops orphans if the foreign key is declared as a constraint and the database enforces it. Many real tables just have a matching column name, so check for orphans yourself.
When a business analyst uses this
During data migration testing, a business analyst runs the LEFT JOIN ... IS NULL check on the migrated orders and customers tables. Each orphan order goes into the RAID log as an issue to fix before sign-off. Otherwise, reports built on inner joins would quietly leave those orders out of revenue totals.
Questions people ask
What is a primary key in a table?
A primary key is the column that names each row once, like customer_id in a customers table. Its values must be unique and can't be empty, so every row can be found by one value.
What is the difference between a primary key and a foreign key in SQL?
A primary key identifies each row in its own table. A foreign key is a column in another table that holds those same values to point back at a row, like customer_id in orders pointing at customers.
Does every table need a primary key?
Most databases let you create a table without one, but almost every table should have one. Without a primary key, duplicate rows can creep in, and other tables have nothing reliable to point at or join on.
