Everyone Can Learn Program
中文 Start learning
Core · DatabasesLesson 6 of 6Free to read

Keys, constraints and indexes · SQL and Databases

How do you design tables that stay correct?

Course contents

Why you need it

Bad data is far more expensive than slow queries. If a table allows an order for a customer who doesn't exist, sooner or later it will contain one. A good design makes wrong data impossible to store, and an index makes the common lookups fast.

The intuition

Design in three questions:

  1. What things are there? One table per kind of thing (customers, products, orders).
  2. What identifies each row? A PRIMARY KEY, usually an integer id.
  3. How do they connect? A foreign key (REFERENCES) in the table on the "many" side: one customer has many orders, so orders holds customer_id.

Store each fact once. If the city lived in every order row, a customer who moves would have to be changed in many places, and one would be missed.

An index is like the index at the back of a book: the database finds matching rows without reading every page.

Definition

PRIMARY KEY

A column whose value is unique and never NULL. It identifies one row.

Index

An extra lookup structure on one or more columns. It speeds up WHERE and JOIN on those columns and costs a little extra space and write time.

Examples

Constraints stop bad data

Run it in the lesson

This table refuses a duplicate email. The INSERT of the second Ada fails, and the error tells you why.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    age INTEGER CHECK (age >= 0)
);
INSERT INTO users (email, age) VALUES ('ada@example.com', 36);
INSERT INTO users (email, age) VALUES ('ada@example.com', 20);

This example stops with an error on purpose: reading the message is the point.

Step by step
  1. L3Every user needs an email, and no two can share one.
  2. L4Negative ages are rejected.
  3. L7Same email again: the database says no.

A foreign key and an index

Run it in the lesson

The order points at a customer that does not exist, so the database refuses it.

CREATE INDEX idx_orders_customer ON orders (customer_id);
INSERT INTO orders (id, customer_id, product_id, qty, day) VALUES (9, 42, 1, 1, '2025-03-09');

This example stops with an error on purpose: reading the message is the point.

Step by step
  1. L1Looking up orders by customer is now fast.
  2. L2There is no customer 42: FOREIGN KEY constraint failed.

Common mistakes

Storing lists in one column

tags = 'red,blue,green' looks easy, but you can't join or count it properly. Make a separate table with one row per tag.

An index on everything

Every index must be updated on every INSERT and UPDATE. Index the columns you search and join on, not every column.

Quick quiz

1. Students take many courses, and each course has many students. How do you store who takes what?

  1. AA course_id column in students
  2. BA third table enrolments(student_id, course_id)
  3. CA text column listing course names
Show the answer

Answer: B

Many-to-many needs a table in the middle, one row per pair. One column in students could hold only one course.

2. Which query is most helped by `CREATE INDEX idx ON orders (customer_id)`?

  1. ASELECT * FROM orders WHERE customer_id = 3
  2. BSELECT * FROM orders WHERE qty > 2
  3. CSELECT COUNT(*) FROM products
Show the answer

Answer: A

The index is on customer_id, so lookups by customer_id can skip straight to the matching rows.

Practice

3 exercises in three steps: predict what a program prints, change a program to meet a new requirement, then write one from scratch. Your code runs in the browser and is checked the moment you submit.

  1. PredictWhat survives?

    Type exactly what this code returns.

  2. ModifyMake bad data impossible

    Change the reviews table so that stars can't be NULL and must be between 1 and 5 (CHECK (stars BETWEEN 1 AND 5)), and product_id must reference products(id). The tests read the table's rules.

  3. WriteA table for tags

    Products can have many tags and a tag can be on many products. Create a table product_tags with product_id (referencing products(id)), tag (TEXT, not NULL) and a primary key made of both columns: PRIMARY KEY (product_id, tag). Insert the tags gift and sale for product 4, then SELECT tag FROM product_tags ORDER BY tag;.

Practise this lesson

Open the interactive lesson to edit and run these examples and solve the exercises with instant feedback. Free to start, no sign-up.

Open the interactive lesson Course map

Interactive lessons open in order. If you have not done the earlier ones yet, you start at the course map.

Terms in this lesson

TermChineseDefinition
Primary key主键The column (or columns) that uniquely identifies a row.
Index索引A lookup structure that makes searches on a column fast.
Schema表结构The design of a database: its tables, columns and rules.