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 you write depends on two kinds of columns, primary keys and foreign keys. Get them right, and your numbers add up. Let's find them in a real online shop database.
Primary key
- Primary key. Start with the customers table. Every customer has a customer ID column. Select the customer ID and the name of every customer. Forty customers, forty different I Ds. That column is the primary key. It names each row once. Never empty, never repeated.
SELECT customer_id, first_name, last_name FROM customers; - Check it is unique. Let's prove it. Group by the customer ID, and keep only the I Ds that show up more than once. No rows. No ID is used twice, so every 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 the order ID. But it also has a customer ID column. Let's see the orders of customer nine. Nine orders, all for customer nine. Here the 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. On says the primary key in customers equals the foreign key in orders. The same nine orders, now with Chloe's name. 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. Now a check every analyst should know. Left join keeps every order, and is null keeps only the orders whose foreign key finds no customer. One order. Order eleven forty one points at customer ninety nine, who doesn't exist. That's 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. So, a primary key names each row once, never repeated. A foreign key in another table points back at it, so one customer can have many orders. Join on the two, and use a left join with is null to find orphans.
