Chapter 6

Chapter 6 — CREATE TABLE and Schema Design

Every table you've queried in this course was created with CREATE TABLE. This chapter explains how to write that statement from scratch — choosing the right columns, applying constraints to enforce data quality, defining relationships with foreign keys, and modifying or removing tables as requirements evolve.

DDL vs DML. CREATE TABLE is a Data Definition Language (DDL) statement — it changes the structure of the database, not its data. DDL statements in MySQL auto-commit immediately and cannot be rolled back, even inside a transaction. Double-check DDL before running it on a production database.

1. CREATE TABLE Anatomy

CREATE TABLE table_name ( column_name data_type [constraints], column_name data_type [constraints], … [table-level constraints] ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ENGINE=InnoDB — the default and only choice for most tables. InnoDB supports transactions, foreign keys, and row-level locking. The older MyISAM engine does not.
DEFAULT CHARSET=utf8mb4 — stores any Unicode character, including emoji. Avoid the misleadingly-named utf8 charset — MySQL's utf8 only stores 3-byte characters and will silently corrupt 4-byte ones (most emoji).
COLLATE=utf8mb4_unicode_ci — case-insensitive comparison. ci = case-insensitive, cs = case-sensitive. Most applications want ci.

IF NOT EXISTS. Add IF NOT EXISTS after CREATE TABLE to prevent an error if the table already exists — the statement becomes a no-op instead of crashing. Useful in setup scripts that may be run more than once:

CREATE TABLE IF NOT EXISTS authors ( … );

2. Constraints

Constraints are rules MySQL enforces on every INSERT and UPDATE. They guarantee the data in a column is always valid — without them, you'd have to replicate the same validation logic in every application that talks to the database.

NOT NULL

column-level

The column must always have a value — MySQL rejects any INSERT or UPDATE that would leave it NULL. Use for columns where "unknown" is never a valid state: title, price, email.

DEFAULT value

column-level

Provides an automatic value when a column is omitted from an INSERT. Common defaults: DEFAULT 0, DEFAULT '', DEFAULT TRUE, DEFAULT CURRENT_TIMESTAMP.

UNIQUE

column or table-level

No two rows may have the same value in this column (or combination of columns). Unlike PRIMARY KEY, a UNIQUE column can contain NULL — and multiple NULLs are allowed because NULL ≠ NULL.

PRIMARY KEY

column or table-level

Combines NOT NULL + UNIQUE. Every table should have one. Uniquely identifies each row. Usually an INT AUTO_INCREMENT surrogate key. Only one PRIMARY KEY per table.

FOREIGN KEY

table-level

Enforces a relationship between tables — the value in this column must exist in the referenced parent table's column. Prevents orphaned child rows. Requires InnoDB.

CHECK

column or table-level

A boolean expression that must be true for every row. Example: CHECK (price > 0), CHECK (stock >= 0). Enforced in MySQL 8.0.16+. Older versions parse but ignore CHECK constraints.

3. The Full Bookshop Schema

Here are all four bookshop tables written from scratch, with every constraint annotated. Notice the creation order — parent tables must exist before child tables that reference them via foreign keys:

author_idINTPK

first_nameVARCHAR(100)NN

last_nameVARCHAR(100)NN

nationalityVARCHAR(60)

book_idINTPK

author_idINTFKNN

titleVARCHAR(255)NN

genreVARCHAR(60)

priceDECIMAL(6,2)NN

stockINTNN

published_yearYEAR

customer_idINTPK

first_nameVARCHAR(100)NN

last_nameVARCHAR(100)NN

emailVARCHAR(255)UQNN

joined_dateDATENN

order_idINTPK

customer_idINTFKNN

book_idINTFKNN

quantityINTNN

total_priceDECIMAL(8,2)NN

order_dateDATETIMENN

-- Step 1: authors — no foreign keys, create first CREATE TABLE IF NOT EXISTS authors ( author_id INT NOT NULL AUTO_INCREMENT, first_name VARCHAR(100) NOT NULL, last_name VARCHAR(100) NOT NULL, nationality VARCHAR(60), -- nullable: may be unknown PRIMARY KEY (author_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Step 2: books — references authors CREATE TABLE IF NOT EXISTS books ( book_id INT NOT NULL AUTO_INCREMENT, author_id INT NOT NULL, title VARCHAR(255) NOT NULL, genre VARCHAR(60), price DECIMAL(6,2) NOT NULL CHECK (price > 0), stock INT NOT NULL DEFAULT 0 CHECK (stock >= 0), published_year YEAR, PRIMARY KEY (book_id), CONSTRAINT fk_books_author FOREIGN KEY (author_id) REFERENCES authors (author_id) ON DELETE RESTRICT ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Step 3: customers — no foreign keys CREATE TABLE IF NOT EXISTS customers ( customer_id INT NOT NULL AUTO_INCREMENT, first_name VARCHAR(100) NOT NULL, last_name VARCHAR(100) NOT NULL, email VARCHAR(255) NOT NULL UNIQUE, joined_date DATE NOT NULL DEFAULT (CURDATE()), PRIMARY KEY (customer_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Step 4: orders — references both customers and books CREATE TABLE IF NOT EXISTS orders ( order_id INT NOT NULL AUTO_INCREMENT, customer_id INT NOT NULL, book_id INT NOT NULL, quantity INT NOT NULL CHECK (quantity > 0), total_price DECIMAL(8,2) NOT NULL, order_date DATETIME NOT NULL DEFAULT NOW(), PRIMARY KEY (order_id), CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers (customer_id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_orders_book FOREIGN KEY (book_id) REFERENCES books (book_id) ON DELETE RESTRICT ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

4. Foreign Key Actions

When a parent row is updated or deleted, MySQL needs to know what to do with the child rows that reference it. You control this with ON DELETE and ON UPDATE actions:

Action	Effect on child rows	Typical use
RESTRICT	Blocks the parent update/delete if child rows exist. The default — and the safest choice.	Default. Prevents accidental data loss. Forces you to deal with children explicitly.
NO ACTION	Same as RESTRICT in MySQL — the error is raised at statement end. Behaviourally identical.	Equivalent to RESTRICT. Mentioned in SQL standard; MySQL treats them the same.
CASCADE	Propagates the change: if parent is deleted, child rows are deleted too. If parent PK is updated, child FK is updated to match.	ON UPDATE CASCADE is very useful (PK changes ripple down). ON DELETE CASCADE is powerful but dangerous — one delete can silently wipe thousands of rows.
SET NULL	Sets the FK column in child rows to NULL when the parent is deleted or updated. The FK column must be nullable.	Soft orphaning — e.g. when an author is deleted, keep their books but clear author_id. Only valid if the FK column allows NULL.
SET DEFAULT	Sets the FK column to its DEFAULT value. Rarely used — InnoDB doesn't support it in most configurations.	Avoid. Poorly supported and almost never the right semantic.

-- Common pattern: restrict deletes, cascade updates CONSTRAINT fk_books_author FOREIGN KEY (author_id) REFERENCES authors (author_id) ON DELETE RESTRICT -- can't delete author if they have books ON UPDATE CASCADE -- if author_id changes, books.author_id follows

Name your constraints. Always give foreign keys an explicit name with CONSTRAINT fk_table_column. MySQL generates one if you don't, but the auto-generated name is unreadable in error messages (fk_books_ibfk_1). A named constraint gives you a clear error: "Cannot delete parent row: constraint fk_books_author" — you know exactly which relationship was violated.

5. Inspecting Tables

-- List all tables in the current database SHOW TABLES; -- Describe a table's columns, types, and constraints DESCRIBE books; -- or the shorter alias: DESC books;

Field	Type	Null	Key	Default	Extra
book_id	int	NO	PRI	NULL	auto_increment
author_id	int	NO	MUL	NULL
title	varchar(255)	NO		NULL
genre	varchar(60)	YES		NULL
price	decimal(6,2)	NO		NULL
stock	int	NO		0
published_year	year	YES		NULL

Key column: PRI = primary key, MUL = non-unique index (here: a foreign key). Default NULL means no default was set — the column must be supplied on INSERT if it's NOT NULL.

-- Show the full CREATE TABLE statement MySQL is using -- Useful for seeing indexes and foreign keys that DESC doesn't show SHOW CREATE TABLE books\G

6. ALTER TABLE — Modifying Structure

Requirements change — columns get added, renamed, resized, or removed. ALTER TABLE modifies an existing table's structure without losing data (mostly — dropping a column deletes its data permanently).

Operation	What it does	Notes
ADD COLUMN	Add a new column to the table	Existing rows get NULL or the DEFAULT value. Adding NOT NULL without a DEFAULT fails if there are existing rows.
DROP COLUMN	Remove a column and all its data permanently	Irreversible. Cannot drop if the column is part of an index or FK.
MODIFY COLUMN	Change a column's type, constraints, or default — keeping the same name	Narrowing a type (VARCHAR 255 → 50) may truncate existing data. MySQL warns but proceeds.
RENAME COLUMN	Rename a column without changing its type	MySQL 8.0+. Updates any indexes on the column automatically.
ADD CONSTRAINT	Add a UNIQUE, CHECK, or FOREIGN KEY constraint after creation	Adding FK or UNIQUE validates all existing rows — fails if violations exist.
DROP CONSTRAINT / DROP FOREIGN KEY	Remove a named constraint	Must use the constraint name. Use SHOW CREATE TABLE to find it.
RENAME TABLE	Rename the table itself	Also works as a standalone `RENAME TABLE old TO new` statement.

-- Add a biography column to authors ALTER TABLE authors ADD COLUMN bio TEXT; -- nullable, existing rows get NULL -- Add a non-nullable column safely: provide a DEFAULT so existing rows aren't rejected ALTER TABLE books ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT TRUE; -- Widen a column that's proving too short ALTER TABLE authors MODIFY COLUMN nationality VARCHAR(100); -- was VARCHAR(60) -- Rename a column (MySQL 8.0+) ALTER TABLE customers RENAME COLUMN joined_date TO registered_date; -- Drop a column (data is gone permanently) ALTER TABLE authors DROP COLUMN bio; -- Add a UNIQUE constraint after the fact ALTER TABLE authors ADD CONSTRAINT uq_author_name UNIQUE (first_name, last_name); -- Add a foreign key after creation ALTER TABLE books ADD CONSTRAINT fk_books_author FOREIGN KEY (author_id) REFERENCES authors (author_id) ON DELETE RESTRICT ON UPDATE CASCADE; -- Drop a foreign key (use the constraint name) ALTER TABLE books DROP FOREIGN KEY fk_books_author; -- Rename the table RENAME TABLE orders TO purchases;

ALTER TABLE locks the table on older MySQL versions. On MySQL 5.x, ALTER TABLE rewrites the entire table, which can lock it for minutes on large datasets. MySQL 8 performs most ALTERs online (no lock), but adding FULLTEXT indexes or changing primary keys still require a table rebuild. On production systems, use a tool like pt-online-schema-change or gh-ost for zero-downtime schema changes.

7. DROP TABLE

DROP TABLE deletes the table entirely — structure and all data. It cannot be rolled back. If you want to keep the structure but remove the data, use TRUNCATE instead.

-- Drop a table (fails if it doesn't exist) DROP TABLE orders; -- Safe version — no error if the table doesn't exist DROP TABLE IF EXISTS orders; -- Drop multiple tables in one statement DROP TABLE IF EXISTS orders, books, customers, authors; -- Drop child tables before parents when FK constraints are present -- Or temporarily disable FK checks to drop in any order (dev/test only) SET FOREIGN_KEY_CHECKS = 0; DROP TABLE IF EXISTS authors, books, customers, orders; SET FOREIGN_KEY_CHECKS = 1; -- always re-enable!

DROP TABLE is irreversible. There is no recycle bin. The data is gone the moment the statement commits. In production, always take a backup before dropping a table — even if you think it's empty. If you're unsure, rename the table first and leave it for a week before dropping.

Chapter Summary

Statement / concept	Key points
CREATE TABLE	Define columns, types, and constraints. Use IF NOT EXISTS for idempotent scripts. Always specify ENGINE=InnoDB and CHARSET=utf8mb4.
NOT NULL	Makes a column mandatory. Use by default; allow NULL only when absence is meaningful.
DEFAULT	Auto-fills a column when omitted from INSERT. Allows adding NOT NULL columns to existing tables safely.
UNIQUE	No duplicate values. NULLs are exempt — multiple NULLs are allowed.
PRIMARY KEY	NOT NULL + UNIQUE. One per table. Usually INT AUTO_INCREMENT.
FOREIGN KEY	Enforces parent-child relationships. Name constraints explicitly. Create parents before children; drop children before parents.
ON DELETE / ON UPDATE	RESTRICT (safe default), CASCADE (propagates change), SET NULL (orphans child row). Avoid SET DEFAULT.
CHECK	Boolean expression enforced on every write. MySQL 8.0.16+. Good for price > 0, stock >= 0.
ALTER TABLE	ADD / DROP / MODIFY / RENAME COLUMN; ADD / DROP CONSTRAINT. DDL auto-commits — no rollback.
DROP TABLE	Deletes table and all data permanently. Use IF NOT EXISTS. Drop children before parents.

Next: Chapter 7 — Aggregate Functions and GROUP BY. How to summarise data across rows: COUNT, SUM, AVG, MIN, MAX, the GROUP BY clause for grouping, and HAVING for filtering groups — plus the difference between WHERE and HAVING.