MySQL – CREATE TABLE, Constraints and ALTER

CREATE TABLE is where you decide what your data is allowed to be. Every constraint you declare here is a rule the database enforces for every writer forever — application code, a migration script, someone at the mysql> prompt at 2am. Constraints in application code only apply to the writers that go through that code.

A table, with its reasoning

This is the pizza schema's product table, near enough as written:

-- unavailable: `product` already exists in the demo database. This is its
-- definition, shown to be read rather than run.
CREATE TABLE product (
    id            BIGINT AUTO_INCREMENT PRIMARY KEY,
    name          VARCHAR(120) NOT NULL,
    description   VARCHAR(500),
    type          VARCHAR(20)  NOT NULL COMMENT 'PIZZA or DRINK',
    image_url     VARCHAR(500),
    active        BOOLEAN      NOT NULL DEFAULT TRUE,
    display_order INT          NOT NULL DEFAULT 0,
    created_at    DATETIME(6)  NOT NULL,
    CONSTRAINT uk_product_name UNIQUE (name)
);

CREATE INDEX idx_product_type_active ON product (type, active);

Reading it: id is a surrogate key — a number with no business meaning. AUTO_INCREMENT assigns it. NOT NULL on name and type says those are required; description is nullable because a product may genuinely not have one. DEFAULT supplies a value when the insert omits the column. COMMENT stores documentation in the schema itself, where SHOW CREATE TABLE will show it to whoever inherits this.

Primary keys

Every table should have one. In InnoDB the primary key is the clustered index — the rows are physically stored in its order — so the choice has consequences beyond identity:

• Keep it small. Every secondary index stores the primary key as its pointer, so a wide key inflates every other index on the table.
• Keep it ascending. AUTO_INCREMENT appends to the end of the B-tree. A random UUID primary key inserts into the middle, causing page splits and fragmentation.
• Declare one explicitly. Without it InnoDB invents a hidden 6-byte key you cannot use.

The pizza schema wants UUIDs in its API — sequential ids let anyone walk /api/orders/1, /2, /3 and read other people's orders — so it carries both: id BIGINT as the key and target of every foreign key, and public_id CHAR(36) as the only identifier the API exposes. That is the standard answer to wanting unguessable ids without paying for a random clustered key.

UNIQUE

Two small tables to demonstrate on — a category, and an item belonging to it:

CREATE TABLE demo_cat (
    id   BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(60) NOT NULL,
    CONSTRAINT uk_demo_cat_name UNIQUE (name)
);

CREATE TABLE demo_item (
    id     BIGINT         AUTO_INCREMENT PRIMARY KEY,
    cat_id BIGINT         NOT NULL,
    price  DECIMAL(10, 2) NOT NULL,
    CONSTRAINT fk_demo_item_cat FOREIGN KEY (cat_id) REFERENCES demo_cat (id) ON DELETE CASCADE,
    CONSTRAINT ck_demo_item_price CHECK (price >= 0)
);

INSERT INTO demo_cat (name) VALUES ('Pizza');

Insert that name a second time and the constraint stops you:

-- ERROR 1062 (23000): Duplicate entry 'Pizza' for key 'demo_cat.uk_demo_cat_name'
INSERT INTO demo_cat (name) VALUES ('Pizza');

A unique constraint is the only way to actually prevent duplicates. "Check whether it exists, then insert" is a race: two requests can both check, both find nothing, and both insert. The database is the only place that can make that atomic.

Name your constraints (uk_product_name). The name appears in the error, which is the difference between an application logging "duplicate entry for key 2" and something you can turn into a message for a user. A UNIQUE constraint creates an index, so it also speeds up lookups on that column. And note that NULLs are exempt — several rows may have a NULL in a unique column, because NULLs are not equal to each other.

Foreign keys

-- ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
INSERT INTO demo_item (cat_id, price) VALUES (999, 5.00);

That is referential integrity: the database refuses to create a row pointing at a category that does not exist. The interesting part is what happens when the parent is deleted, and the pizza schema deliberately answers it both ways:

Cascade is powerful and worth respecting: one DELETE can silently remove a great deal. It works because the pizza schema's line items genuinely belong to their order and nothing else references them.

INSERT INTO demo_item (cat_id, price) VALUES (1, 9.99), (1, 12.99);
SELECT COUNT(*) AS items_before FROM demo_item;

+--------------+
| items_before |
+--------------+
|            2 |
+--------------+

DELETE FROM demo_cat WHERE id = 1;
SELECT COUNT(*) AS items_after FROM demo_item;

+-------------+
| items_after |
+-------------+
|           0 |
+-------------+

One row deleted, two more went with it. Foreign keys also need the referenced column to be indexed, and MySQL creates an index on the child column automatically if you have not.

NOT NULL and CHECK

-- ERROR 1048 (23000): Column 'name' cannot be null
INSERT INTO demo_cat (name) VALUES (NULL);

-- ERROR 3819 (HY000): Check constraint 'ck_demo_item_price' is violated.
INSERT INTO demo_item (cat_id, price) VALUES (1, -1.00);

CHECK constraints are enforced from MySQL 8.0.16 — before that they parsed and did nothing, which is why older advice says not to bother. They are a good fit for things a type cannot express: a non-negative price, an end date after a start date, a status restricted to a known list.

Default to NOT NULL. Every nullable column is a branch every query has to handle; add it only when you can say what NULL means there. See NULL and IS NULL.

ALTER TABLE

ALTER TABLE demo_cat ADD COLUMN sort_order INT NOT NULL DEFAULT 0;
ALTER TABLE demo_cat MODIFY COLUMN name VARCHAR(100) NOT NULL;
ALTER TABLE demo_cat ADD CONSTRAINT uk_demo_cat_sort UNIQUE (sort_order);

MODIFY takes the whole new definition and silently drops anything you leave out — omit NOT NULL and the column becomes nullable. CHANGE does the same and also renames, so it needs the old and new name both. This is a good reason to keep migrations in files and review them.

Adding a column with a default is instant in MySQL 8 (an INSTANT operation that only touches metadata). Many other changes are not: they copy the whole table. On a large table that means a long operation holding locks — see running queries in production before you run one against something real.

Reading a table back

DESCRIBE product;             -- the columns, briefly
SHOW CREATE TABLE product;    -- the full definition, constraints and all
SHOW INDEX FROM product;      -- what is indexed

SHOW CREATE TABLE is the one to reach for: it gives you exactly what MySQL would need to recreate the table, including the constraints and their names.

Migrations, not ad-hoc DDL

The pizza schema is not maintained by hand — it is nine ordered migration files applied by Liquibase, each with a rollback. That matters because the schema has to be recreated identically on every developer's machine, in CI, and in production, and because "what changed and when" needs an answer. Flyway is the other common choice; so is Django or Rails' built-in system. Pick one before your second environment exists.

What to remember

• Constraints in the database apply to every writer; constraints in code do not.
• BIGINT AUTO_INCREMENT primary key, small and ascending. Add a UUID column if the API needs unguessable ids.
• Name your constraints — the name is what the error message gives you.
• ON DELETE is a real decision: CASCADE, SET NULL or RESTRICT.
• MODIFY replaces the entire column definition. Repeat every clause.