With the server running, it is time to give Orbit a home. This lesson creates the four tables the rest of the course uses and explains every choice along the way.
Working with databases
You created orbit in lesson 2. These are the statements for managing databases in general:
CREATE DATABASE IF NOT EXISTS scratch;
SHOW DATABASES;
USE scratch;
SELECT DATABASE();
DROP DATABASE IF EXISTS scratch;
IF NOT EXISTSandIF EXISTSturn errors into warnings, which keeps scripts re-runnable.USEsets the default database for unqualified table names. You can always qualify instead:orbit.notes.DROP DATABASEdeletes every table inside immediately and permanently. There is no recycle bin; only a backup brings it back.
Character sets and collations
A character set decides which characters a text column can store. A collation decides how text is compared and sorted.
Always use utf8mb4. It stores every Unicode character, including emoji and many Asian scripts. The older utf8 (an alias for utf8mb3) only stores up to three bytes per character, so '🚀' fails or is mangled, and it is deprecated.
The default collation since MySQL 8.0 is utf8mb4_0900_ai_ci:
| Part | Meaning |
|---|---|
0900 | Unicode Collation Algorithm 9.0 rules |
ai | Accent-insensitive: e equals é |
ci | Case-insensitive: A equals a |
So by default, WHERE name = 'ana silva' finds Ana Silva. That is usually what users expect from a search, but it surprises developers coming from PostgreSQL. When you need exact matching, choose a different collation for that column or comparison:
| Collation | Case | Accents | Use for |
|---|---|---|---|
utf8mb4_0900_ai_ci | ignored | ignored | Names, titles, general text (default) |
utf8mb4_0900_as_ci | ignored | significant | Text where accents change meaning |
utf8mb4_0900_as_cs | significant | significant | Case-sensitive codes |
utf8mb4_bin | byte by byte | byte by byte | Tokens, hashes, exact identifiers |
Character set and collation can be set per server, database, table, column and even per expression:
SELECT name FROM users WHERE name = 'ana silva' COLLATE utf8mb4_0900_as_cs;
Design the Orbit tables
Switch to the orbit database and create the first two tables:
USE orbit;
CREATE TABLE users (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
email VARCHAR(255) NOT NULL,
name VARCHAR(100) NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uq_users_email (email)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;
CREATE TABLE notes (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
user_id INT UNSIGNED NOT NULL,
title VARCHAR(200) NOT NULL,
body TEXT NOT NULL DEFAULT (''),
pinned BOOLEAN NOT NULL DEFAULT FALSE,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
KEY idx_notes_user (user_id),
CONSTRAINT fk_notes_user FOREIGN KEY (user_id)
REFERENCES users (id) ON DELETE CASCADE
);
Query OK, 0 rows affected (0.03 sec)
Query OK, 0 rows affected (0.04 sec)
Here is what each piece does:
| Piece | Meaning |
|---|---|
INT UNSIGNED | Whole numbers from 0 to about 4.29 billion; ids are never negative |
AUTO_INCREMENT | MySQL assigns 1, 2, 3… when you insert without an id |
PRIMARY KEY (id) | Uniquely identifies a row; InnoDB stores the table ordered by it |
UNIQUE KEY uq_users_email | No two users share an email; also creates an index |
DEFAULT CURRENT_TIMESTAMP | Filled in with the current time when not provided |
ON UPDATE CURRENT_TIMESTAMP | Refreshed automatically whenever the row changes |
DEFAULT ('') | An expression default in parentheses, needed for TEXT columns (MySQL 8.0.13+) |
BOOLEAN | A synonym for TINYINT(1): FALSE is 0, TRUE is 1 |
KEY idx_notes_user | A normal index to find a user’s notes quickly |
FOREIGN KEY ... ON DELETE CASCADE | Every user_id must exist in users; deleting a user deletes their notes |
ENGINE = InnoDB and DEFAULT CHARSET = utf8mb4 are already the defaults; writing them on the first table just makes the choice visible. The notes table inherits both.
Now tags, and the table that links notes to tags. A note can have many tags and a tag many notes, so this many-to-many relationship needs a table of its own:
CREATE TABLE tags (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY uq_tags_name (name)
);
CREATE TABLE note_tags (
note_id INT UNSIGNED NOT NULL,
tag_id SMALLINT UNSIGNED NOT NULL,
PRIMARY KEY (note_id, tag_id),
KEY idx_note_tags_tag (tag_id),
CONSTRAINT fk_note_tags_note FOREIGN KEY (note_id) REFERENCES notes (id) ON DELETE CASCADE,
CONSTRAINT fk_note_tags_tag FOREIGN KEY (tag_id) REFERENCES tags (id) ON DELETE CASCADE
);
The composite primary key (note_id, tag_id) stops the same tag being attached to a note twice. The extra index on tag_id makes “all notes with tag X” fast, because the primary key only helps lookups that start with note_id.
Note: A foreign key column must have exactly the same type as the column it references, including UNSIGNED. INT referencing INT UNSIGNED fails with error 3780.
Inspect what you built
SHOW TABLES;
+-----------------+
| Tables_in_orbit |
+-----------------+
| note_tags |
| notes |
| tags |
| users |
+-----------------+
4 rows in set (0.00 sec)
DESCRIBE gives a compact view; SHOW CREATE TABLE users\G prints the full statement MySQL would use to recreate it, including defaults you did not write.
Change a table with ALTER TABLE
Orbit’s product team adds paid plans. Every user needs a plan, free by default:
ALTER TABLE users
ADD COLUMN plan ENUM('free', 'pro') NOT NULL DEFAULT 'free' AFTER name;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
DESCRIBE users;
+------------+--------------------+------+-----+-------------------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------------+------+-----+-------------------+-------------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| email | varchar(255) | NO | UNI | NULL | |
| name | varchar(100) | NO | | NULL | |
| plan | enum('free','pro') | NO | | free | |
| created_at | datetime | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+------------+--------------------+------+-----+-------------------+-------------------+
5 rows in set (0.00 sec)
AFTER name places the column; without it, new columns go last. ENUM limits the value to a fixed list; lesson 5 discusses when that is a good idea.
The most common ALTER TABLE operations:
ALTER TABLE notes ADD COLUMN archived BOOLEAN NOT NULL DEFAULT FALSE;
ALTER TABLE notes MODIFY COLUMN title VARCHAR(250) NOT NULL;
ALTER TABLE notes RENAME COLUMN archived TO is_archived;
ALTER TABLE notes ALTER COLUMN is_archived SET DEFAULT TRUE;
ALTER TABLE notes DROP COLUMN is_archived;
ALTER TABLE notes ADD INDEX idx_notes_created (created_at);
ALTER TABLE notes DROP INDEX idx_notes_created;
MODIFYchanges a column’s definition; you must repeat everything you want to keep, includingNOT NULL, or it is lost.CHANGE COLUMN old new definitionrenames and redefines in one step.- Several changes can go in one
ALTER TABLE, separated by commas, which rebuilds the table once instead of several times.
Tip: InnoDB performs many changes online: adding a column, an index or changing a default does not block reads and writes. ALGORITHM=INSTANT adds or drops a column in milliseconds even on huge tables (MySQL 8.0.29+ for any position). Add ALGORITHM=INSTANT explicitly and MySQL refuses instead of silently falling back to a slow copy.
Rename, copy, empty and drop
RENAME TABLE notes TO notes_old; -- instant
RENAME TABLE notes_old TO notes;
CREATE TABLE notes_backup LIKE notes; -- same columns and indexes, no rows
INSERT INTO notes_backup SELECT * FROM notes;
TRUNCATE TABLE notes_backup; -- delete every row, reset AUTO_INCREMENT
DROP TABLE IF EXISTS notes_backup; -- remove the table
CREATE TABLE ... LIKE copies the structure including indexes but not foreign keys. CREATE TABLE new AS SELECT ... copies data but only basic column types, without indexes. TRUNCATE is much faster than DELETE for emptying a table, but it cannot run on a table referenced by a foreign key and cannot be rolled back.
Temporary tables
A temporary table exists only for your session and disappears when you disconnect:
CREATE TEMPORARY TABLE recent_ids AS
SELECT id FROM notes WHERE created_at > NOW() - INTERVAL 7 DAY;
Other sessions cannot see it, and two sessions can each have a temporary table with the same name. They are handy for multi-step reports; lesson 10 shows CTEs, which often replace them.
Names and case sensitivity
- Database and table names map to files, so their case sensitivity follows the operating system: case-sensitive on Linux, insensitive on Windows and macOS (controlled by
lower_case_table_names, which can only be set when the server is initialised). - Column, index and alias names are always case-insensitive.
- Quote identifiers with backticks when they clash with keywords or contain spaces: `
order,user notes`. Better: pick names that never need quoting.
A safe convention that works everywhere is lowercase snake_case: note_tags, created_at.
Quick reference
| Task | SQL |
|---|---|
| Create a database | CREATE DATABASE orbit CHARACTER SET utf8mb4; |
| Create a table | CREATE TABLE t (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, ...); |
| Show structure | DESCRIBE t; / SHOW CREATE TABLE t\G |
| Add / change / drop a column | ALTER TABLE t ADD / MODIFY / DROP COLUMN ...; |
| Rename a table | RENAME TABLE a TO b; |
| Copy structure | CREATE TABLE b LIKE a; |
| Empty a table | TRUNCATE TABLE t; |
| Remove a table | DROP TABLE IF EXISTS t; |
Exercise
Why should new tables use utf8mb4 rather than utf8?
Show answer
Answer: A. MySQL’s old utf8 stores at most three bytes per character and is deprecated. utf8mb4 is real UTF-8 and stores every Unicode character.
Exercise
You run ALTER TABLE notes MODIFY COLUMN title VARCHAR(250); on a column that was VARCHAR(200) NOT NULL. What happens to NOT NULL?
Show answer
Answer: B. MODIFY replaces the whole column definition. Attributes you do not repeat, such as NOT NULL, DEFAULT or COMMENT, are dropped.
Exercise
With the default collation utf8mb4_0900_ai_ci, which comparison is true?
Show answer
Answer: C. ai means accent-insensitive and ci case-insensitive, so both pairs compare equal. Use an _as_cs or _bin collation when exact matching matters.
Key takeaways
- Databases are created with
utf8mb4; the default collation ignores case and accents. - Give every table an
AUTO_INCREMENTprimary key or a natural composite key, and name your keys and constraints. - Foreign keys need matching column types and give you cascading deletes.
MODIFYrewrites the whole column definition; repeat every attribute you want to keep.- Table names are case-sensitive on Linux: stick to lowercase snake_case.
FAQ
Should my primary keys be INT, BIGINT or UUID?
INT UNSIGNED covers about 4.29 billion rows, plenty for most tables; use BIGINT UNSIGNED for event logs and anything that may grow past that. UUIDs are useful when ids must be generated outside the database, but random UUIDs scatter inserts across the clustered index; store them as BINARY(16) with UUID_TO_BIN(UUID(), 1) to keep them ordered and compact.
Does AUTO_INCREMENT reuse ids of deleted rows?
No. Deleted ids leave gaps, and failed inserts and rollbacks also consume ids. Never rely on ids being consecutive; use them only as identifiers.
What is the difference between KEY and INDEX in CREATE TABLE?
None. They are synonyms. UNIQUE KEY and UNIQUE INDEX are also the same.
Can I undo DROP TABLE?
Not with SQL. DDL statements commit immediately and cannot be rolled back. Restore from a backup, which lesson 19 sets up, and consider renaming tables to old_name_deleted for a few days before dropping them.