SQL Primary Keys and Foreign Keys Explained for Beginners

# What are keys? A key is a column that identifies rows. A primary key is a row's own id. A foreign key is a column holding the id of a row in another table. Why you need them: two customers can have the same name, so the database needs a value that tells every row apart. And an order has to say which customer placed it, without copying the customer's name into every order. - `PRIMARY KEY` the row's own unique id, like id in orders - `FOREIGN KEY` a column that points at another table's primary key, like customer_id in orders Here every order has its own id, and its customer_id points at a row in customers. > Keys are what JOINs follow later on: orders.customer_id = customers.id.

SELECT id, customer_id, qty
FROM orders
LIMIT 3

# Primary key: a unique id for every row Two customers can have the same name. A primary key gives each row a value no other row has, so you can always point at exactly one row. - `PRIMARY KEY` unique, and never empty > Use a plain number that means nothing else as the key. Names repeat and emails change; an id never has to.

CREATE TABLE drivers (
  id INT PRIMARY KEY,
  name VARCHAR(50));
INSERT INTO drivers
VALUES (1, 'Zoe'), (2, 'Max');
SELECT * FROM drivers

# Duplicate keys are refused - `INSERT INTO drivers VALUES (1, 'Zed')` fails: id 1 is already taken You'll see "UNIQUE constraint failed": you broke the rule that each key appears once. MySQL words it as "Duplicate entry '1' for key 'PRIMARY'". > In an interview, "what is a primary key?" wants two words: unique and not null.

CREATE TABLE drivers (
  id INT PRIMARY KEY,
  name VARCHAR(50));
INSERT INTO drivers
VALUES (1, 'Zoe');
INSERT INTO drivers
VALUES (1, 'Zed')

# AUTO_INCREMENT: the database numbers the rows - `AUTO_INCREMENT` each new row gets the next number: 1, 2, 3 - `INSERT INTO drivers (name)` leave id out, and the database fills it in > A number is never reused. Delete row 3 and the next new row still gets 4, so gaps in ids are normal.

CREATE TABLE drivers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50)
);
INSERT INTO drivers (name)
VALUES ('Zoe'), ('Max'), ('Ali');
SELECT * FROM drivers

# Foreign key: a link to another table The orders table doesn't store the customer's name. It stores customer_id: the id of a row in customers. - `FOREIGN KEY` a column that holds another table's primary key - `customer_id` in orders points at id in customers "Foreign" just means "from another table". > Most JOINs you'll write follow a foreign key: orders.customer_id = customers.id.

SELECT id, customer_id, qty
FROM orders

# The foreign key rule A foreign key must point at a row that exists. There is no customer 99, so this INSERT is refused with "FOREIGN KEY constraint failed". - `constraint` a rule the database enforces > The database checks this rule, not your app. Even a buggy script can't create an order for a customer who doesn't exist.

INSERT INTO orders (id, customer_id,
  pizza_id, qty, status)
VALUES (13, 99, 1, 1, 'delivered')

# Create a foreign key - `FOREIGN KEY (pizza_id)` this column is a foreign key - `REFERENCES pizzas(id)` it must match an id in the pizzas table Now every review has to be about a real pizza. > Joins run on foreign key columns, so they need an index. MySQL adds one for you; most other databases don't.

CREATE TABLE reviews (
  id INT PRIMARY KEY,
  pizza_id INT,
  stars INT,
  FOREIGN KEY (pizza_id) REFERENCES
    pizzas(id)
);
INSERT INTO reviews
VALUES (1, 2, 5);
SELECT * FROM reviews