MENU


Defining Tables

A table definition is a contract that MySQL enforces on every write, whichever client sends it: column types, NOT NULL, unique keys, CHECK expressions and foreign keys. This section builds and alters the bookshop schema below, which sample-db.sql loads and the rest of the chapter queries. Section 3.3 chose its types; Section 3.10 explains why it has these six tables, and Section 3.11 indexes them.Figure 3.3: The bookshop schema: six tables, with arrows from each foreign key to the key it references

3.4.1 CREATE TABLE and Column Definitions

Each column in CREATE TABLE is a name, a type (Section 3.3) and attributes such as NOT NULL, DEFAULT, AUTO_INCREMENT, UNIQUE, COMMENT or INVISIBLE; multi-column keys follow the columns. Since MySQL 8.0.13 a default may be an expression in parentheses:

CREATE TABLE coupons (

code CHAR(8) NOT NULL DEFAULT (UPPER(LEFT(REPLACE(UUID(), '-', ''), 8))),

valid_until DATE NOT NULL DEFAULT (CURRENT_DATE + INTERVAL 30 DAY),

PRIMARY KEY (code)

);

SHOW CREATE TABLE customers\G

Listing 3.31: Expression defaults, and the stored definition of the sample customers table

*************************** 1. row ***************************

Table: customers

Create Table: CREATE TABLE `customers` (

`id` int unsigned NOT NULL AUTO_INCREMENT,

`email` varchar(255) NOT NULL,

`name` varchar(100) NOT NULL,

`country` char(2) NOT NULL,

`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,

PRIMARY KEY (`id`),

UNIQUE KEY `email` (`email`)

) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Output of Listing 3.31 Two rows inserted with INSERT INTO coupons () VALUES (), () got codes 0977974F and 0977AB21, both valid until 2026-10-23. SHOW CREATE TABLE prints what the server stored, not what sample-db.sql typed: the inline UNIQUE became a key named email, the engine and collation came from server defaults, and AUTO_INCREMENT=9 follows the eight sample customers.CREATE TABLE customers_archive LIKE customers copies the definition and indexes, without foreign keys or rows; CREATE TABLE country_counts AS SELECT ... copies rows with bare column types. CREATE TABLE IF NOT EXISTS on an existing table only raises Note 1050 and never compares definitions, so it hides schema drift.

3.4.2 Primary Keys, AUTO_INCREMENT, and Generated Invisible Primary Keys

InnoDB orders each table by its primary key (Section 3.11.2) and stores that key in every secondary index, so keep it short and stable: INT UNSIGNED AUTO_INCREMENT for entities, the two foreign keys for a link table such as order_items (natural keys: Section 3.10.8).A table without a primary key gets a hidden row id you cannot query or replicate by. Since MySQL 8.0.30, sql_generate_invisible_primary_key adds a real one instead, my_row_id, as an invisible column (MySQL 8.0.23): SELECT * and column-less INSERTs skip it.

INSERT INTO customers (email, name, country) VALUES ('ivan@example.com', 'Ivan Petrov', 'BG');

INSERT INTO customers (email, name, country) VALUES ('ivan@example.com', 'Ivan P.', 'BG');

INSERT INTO customers (email, name, country) VALUES ('jo@example.com', 'Jo Park', 'KR');

SELECT id, email FROM customers WHERE id > 8;

SET SESSION sql_generate_invisible_primary_key = ON;

CREATE TABLE page_views (url VARCHAR(255) NOT NULL, viewed_at DATETIME NOT NULL);

INSERT INTO page_views VALUES ('/books/1', NOW()), ('/books/2', NOW());

Listing 3.32: A gap in AUTO_INCREMENT, then a generated invisible primary key

ERROR 1062 (23000): Duplicate entry 'ivan@example.com' for key 'customers.email'

+----+------------------+

| id | email |

+----+------------------+

| 9 | ivan@example.com |

| 11 | jo@example.com |

+----+------------------+

Output of Listing 3.32 The failed insert consumed id 10, and rollbacks leave gaps too, so never use these ids as a gapless sequence such as invoice numbers. SHOW CREATE TABLE page_views lists ` my_row_id bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */ ; SELECT * returned only url and viewed_at, and dropping the key failed with error 1235. sql_require_primary_key = ON` rejects keyless tables outright.

3.4.3 UNIQUE, NOT NULL, and CHECK Constraints

NOT NULL makes a value mandatory, UNIQUE rejects duplicates but allows many NULLs, and CHECK (expr) rejects rows for which the expression is false (NULL passes). MySQL ignored CHECK until 8.0.16; it is now enforced on every insert and update.

INSERT INTO reviews (product_id, customer_id, rating, created_at) VALUES (1, 2, 6, NOW());

INSERT INTO customers (email, name, country) VALUES (NULL, 'Nobody', 'US');

ALTER TABLE orders ADD COLUMN shipped_at DATETIME NULL,

ADD CONSTRAINT chk_shipped_after CHECK (shipped_at IS NULL OR shipped_at >= ordered_at);

UPDATE orders SET shipped_at = '2026-03-01 09:00:00' WHERE id = 1;

ALTER TABLE orders ADD CONSTRAINT chk_past CHECK (ordered_at <= NOW());

Listing 3.33: Violating the constraints of the bookshop schema

ERROR 3819 (HY000): Check constraint 'reviews_chk_1' is violated.

ERROR 1048 (23000): Column 'email' cannot be null

Query OK, 9 rows affected (0.124 sec)

Records: 9 Duplicates: 0 Warnings: 0

ERROR 3819 (HY000): Check constraint 'chk_shipped_after' is violated.

ERROR 3814 (HY000): An expression of a check constraint 'chk_past' contains disallowed

function: now.

Output of Listing 3.33 Name your checks: the error reports the name, and DROP CHECK needs it. Expressions must be deterministic, so NOW(), RAND() and subqueries are refused. ALTER CHECK chk_shipped_after NOT ENFORCED suspends a check for a bulk import; switching back to ENFORCED re-validates every row and failed with error 3819 until the bad row was fixed.

3.4.4 Foreign Keys and Referential Actions

A foreign key requires a child value to exist in the parent: orders.customer_id must be a customers.id. The default action, NO ACTION (the same as RESTRICT), rejects a parent change while children exist; CASCADE deletes or updates the children, and SET NULL clears their column. The listing tests the default, then makes order lines die with their order and lets a subcategory outlive its parent:

INSERT INTO orders (customer_id, ordered_at) VALUES (99, NOW());

DELETE FROM customers WHERE id = 1;

ALTER TABLE order_items

DROP FOREIGN KEY order_items_ibfk_1,

ADD CONSTRAINT fk_items_order FOREIGN KEY (order_id) REFERENCES orders (id)

ON DELETE CASCADE;

ALTER TABLE categories

DROP FOREIGN KEY categories_ibfk_1,

ADD CONSTRAINT fk_category_parent FOREIGN KEY (parent_id) REFERENCES categories (id)

ON DELETE SET NULL ON UPDATE CASCADE;

INSERT INTO categories (name, parent_id) VALUES ('Gifts', NULL), ('Posters', 6);

DELETE FROM orders WHERE id = 5;

DELETE FROM categories WHERE name = 'Gifts';

SELECT (SELECT COUNT(*) FROM order_items WHERE order_id = 5) AS items_left,

(SELECT parent_id FROM categories WHERE name = 'Posters') AS posters_parent;

Listing 3.34: Foreign key violations, then CASCADE and SET NULL

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails

(`shop`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES

`customers` (`id`))

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails

(`shop`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES

`customers` (`id`))

+------------+----------------+

| items_left | posters_parent |

+------------+----------------+

| 0 | NULL |

+------------+----------------+

Output of Listing 3.34 Name keys you may change; DROP FOREIGN KEY needs the name. Types and signs must match (error 3780), and the parent must be a primary or unique key (error 6125). SET SESSION foreign_key_checks = 0 skips checks for bulk loads, and nothing re-validates afterward: an order for customer 99 went in and stayed. Warning: MySQL 9.7.2 accepted ON DELETE SET DEFAULT, which the manual says InnoDB rejects, then refused the delete with error 1451 like NO ACTION. Do not use it. Under the hood: Since MySQL 9.6 the SQL layer enforces foreign keys, not InnoDB (innodb_native_foreign_keys = OFF), so cascaded changes now reach the binary log.

3.4.5 Table Options: Engine, Character Set, and Row Format

The options after the closing parenthesis apply to the whole table. Keep ENGINE=InnoDB, the default, for application data (Section 3.14.10 covers the others): a foreign key to a MyISAM table fails with error 1824, and a misspelled or MariaDB-only engine such as Aria fails with error 1286 instead of silently becoming InnoDB. DEFAULT CHARSET and COLLATE set the column defaults (Section 3.3.8). ROW_FORMAT=DYNAMIC, the default, moves long TEXT, BLOB and JSON values off-page behind a 20-byte pointer; COMPACT keeps a 768-byte prefix inline, REDUNDANT is the legacy format, and COMPRESSED is in Section 3.4.9. AUTO_INCREMENT = 1000 starts numbering higher, and COMMENT = '...' documents the table; information_schema.TABLES reports every option.

3.4.6 ALTER TABLE and Online DDL

ALTER TABLE adds, drops, renames and modifies columns, indexes, constraints and options, several per statement. ALGORITHM decides how: INSTANT edits only the data dictionary (adding, dropping or renaming a column, or changing a default, since 8.0.29); INPLACE works inside the tablespace while other sessions keep writing (LOCK=NONE); COPY rebuilds the table and blocks writes. Name the one you expect, so the statement fails instead of silently copying a big table:

CREATE TABLE order_events (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

order_id INT UNSIGNED NOT NULL, event VARCHAR(20) NOT NULL, amount DECIMAL(8,2) NOT NULL);

SET SESSION cte_max_recursion_depth = 2000000;

INSERT INTO order_events (order_id, event, amount)

WITH RECURSIVE n (i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 2000000)

SELECT i % 9 + 1, ELT(i % 3 + 1, 'created', 'paid', 'shipped'), i % 500 / 10 FROM n;

ALTER TABLE order_events

ADD COLUMN channel VARCHAR(10) NOT NULL DEFAULT 'web', ALGORITHM=INSTANT;

SELECT TABLE_ID, TOTAL_ROW_VERSIONS FROM information_schema.INNODB_TABLES

WHERE NAME = 'shop/order_events';

ALTER TABLE order_events ADD COLUMN source VARCHAR(10) NOT NULL DEFAULT 'web', ALGORITHM=COPY;

SELECT TABLE_ID, TOTAL_ROW_VERSIONS FROM information_schema.INNODB_TABLES

WHERE NAME = 'shop/order_events';

ALTER TABLE order_events MODIFY amount DECIMAL(10,2) NOT NULL, ALGORITHM=INSTANT;

ALTER TABLE order_events ADD INDEX idx_order (order_id), ALGORITHM=INPLACE, LOCK=NONE;

Listing 3.35: The same change made instantly and by copying, and one INSTANT cannot make

Query OK, 0 rows affected (0.030 sec)

+----------+--------------------+

| TABLE_ID | TOTAL_ROW_VERSIONS |

+----------+--------------------+

| 1406 | 1 |

+----------+--------------------+

Query OK, 2000000 rows affected (8.903 sec)

+----------+--------------------+

| TABLE_ID | TOTAL_ROW_VERSIONS |

+----------+--------------------+

| 1421 | 0 |

+----------+--------------------+

ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: Need to rebuild the table to

change column type. Try ALGORITHM=COPY/INPLACE.

Query OK, 0 rows affected (3.407 sec)

Output of Listing 3.35 The instant add took 0.03 seconds and rewrote 0 rows: InnoDB recorded a new row version, and reading an old row supplies 'web' from the dictionary. By COPY the same change took 8.9 seconds and built a new table (new TABLE_ID, versions reset). Since MySQL 9.1 a table allows 255 instant versions (64 before); then error 4092 demands a rebuild such as OPTIMIZE TABLE. The index took 3.4 seconds in place, with LOCK=NONE leaving the table open to writes.Even an instant change takes a brief exclusive metadata lock, which one long transaction can stall, queueing every later query (Section 3.14.7). For rebuilds of busy tables, gh-ost (https://github.com/github/gh-ost) and pt-online-schema-change from Percona Toolkit (https://github.com/percona/percona-toolkit) copy in small chunks and swap at the end.

3.4.7 RENAME, TRUNCATE, and DROP TABLE

RENAME TABLE a TO b, c TO d renames atomically, so a rebuilt copy can replace a table without any query finding it missing, and RENAME TABLE shop.a TO archive.a moves a table between databases.

RENAME TABLE customers_archive TO customers_old, country_counts TO stats_by_country;

DROP TABLE customers;

DROP TABLE customers_old, no_such_table;

CREATE TABLE products_new LIKE products;

INSERT INTO products_new SELECT * FROM products;

RENAME TABLE products TO products_old, products_new TO products;

SELECT CONSTRAINT_NAME, REFERENCED_TABLE_NAME FROM information_schema.REFERENTIAL_CONSTRAINTS

WHERE CONSTRAINT_SCHEMA = 'shop' AND TABLE_NAME = 'order_items';

Listing 3.36: Refused and atomic drops, then a table swap and where its foreign key went

ERROR 3730 (HY000): Cannot drop table 'customers' referenced by a foreign key constraint

'reviews_ibfk_2' on table 'reviews'.

ERROR 1051 (42S02): Unknown table 'shop.no_such_table'

+--------------------+-----------------------+

| CONSTRAINT_NAME | REFERENCED_TABLE_NAME |

+--------------------+-----------------------+

| fk_items_order | orders |

| order_items_ibfk_2 | products_old |

+--------------------+-----------------------+

Output of Listing 3.36 Foreign keys follow the table, not the name: order_items now points at products_old, and the new products has no foreign keys because LIKE skips them, so re-point child keys before dropping the old copy. DROP TABLE is atomic since MySQL 8.0: the failed statement left customers_old in place. IF EXISTS turns error 1051 into a note. TRUNCATE TABLE (compared with DELETE in Section 3.2.5) re-creates the table, commits implicitly and resets AUTO_INCREMENT: after truncating page_views, the next row got my_row_id 1.

3.4.8 Generated Columns and Functional Indexes

A generated column, col type AS (expr), is computed from its row: VIRTUAL (the default) on every read, STORED on every write. Both can be indexed, the expression must be deterministic, and you cannot write to them.

ALTER TABLE products

ADD COLUMN format VARCHAR(20) AS (attributes->>'$.format') VIRTUAL AFTER price,

ADD INDEX idx_format (format);

ALTER TABLE order_items

ADD COLUMN line_total DECIMAL(10,2) AS (quantity * unit_price) STORED;

CREATE INDEX idx_pages ON products ((CAST(attributes->>'$.pages' AS UNSIGNED)));

SELECT sku, format, price FROM products WHERE format = 'hardcover';

UPDATE order_items SET line_total = 0 WHERE order_id = 1;

Listing 3.37: A virtual column over JSON, a stored line total, and a functional index

+-----------+-----------+-------+

| sku | format | price |

+-----------+-----------+-------+

| BK-LAR-01 | hardcover | 49.00 |

+-----------+-----------+-------+

ERROR 3105 (HY000): The value specified for generated column 'line_total' in table

'order_items' is not allowed.

Output of Listing 3.37 The virtual column needed no rebuild, the stored one rewrote the 15 order lines, and EXPLAIN shows Index lookup on products using idx_format; Section 3.9.6 indexes JSON arrays the same way. A functional index (MySQL 8.0.13) indexes an expression directly and is really a hidden virtual column, which SHOW EXTENDED COLUMNS lists as !hidden!idx_pages!0!0. Queries must repeat the expression exactly: WHERE CAST(attributes->>'$.pages' AS UNSIGNED) > 400 got an Index range scan ... using idx_pages. An index on (RAND()) fails with error 3758.

3.4.9 Tablespaces, Compression, and Table Encryption

Each table normally has its own file, /var/lib/mysql/shop/orders.ibd. A general tablespace (CREATE TABLESPACE shop_archive ADD DATAFILE 'shop_archive.ibd', then ALTER TABLE orders_2025 TABLESPACE shop_archive) holds several tables and belongs to the server: it survived DROP DATABASE in testing.ROW_FORMAT=COMPRESSED squeezes 16 KB pages into KEY_BLOCK_SIZE KB; COMPRESSION='zlib' or 'lz4' compresses pages on write and punches holes in the file. The innodb_file_format setting in old guides was removed in MySQL 8.0.

CREATE TABLE log_plain (id INT AUTO_INCREMENT PRIMARY KEY, msg TEXT NOT NULL);

CREATE TABLE log_page LIKE log_plain; ALTER TABLE log_page COMPRESSION = 'zlib';

CREATE TABLE log_row LIKE log_plain;

ALTER TABLE log_row ROW_FORMAT = COMPRESSED KEY_BLOCK_SIZE = 8;

INSERT INTO log_plain (msg)

SELECT CONCAT('GET /books/', id, ' 200 ', REPEAT('Mozilla/5.0 (X11; Linux x86_64) ', 4))

FROM order_events LIMIT 300000;

INSERT INTO log_page (msg) SELECT msg FROM log_plain;

INSERT INTO log_row (msg) SELECT msg FROM log_plain;

SELECT NAME, FILE_SIZE, ALLOCATED_SIZE FROM information_schema.INNODB_TABLESPACES

WHERE NAME LIKE 'shop/log%';

Listing 3.38: 300,000 log lines uncompressed, page-compressed and row-compressed

+----------------+-----------+----------------+

| NAME | FILE_SIZE | ALLOCATED_SIZE |

+----------------+-----------+----------------+

| shop/log_plain | 67108864 | 67112960 |

| shop/log_page | 67108864 | 24535040 |

| shop/log_row | 31457280 | 31461376 |

+----------------+-----------+----------------+

Output of Listing 3.38 Page compression allocated 23 MB behind an apparent 64 MB, which ls -l still reports; row compression made a real 30 MB file. Both cost CPU on writes.ENCRYPTION='Y' encrypts a tablespace at rest, and without a keyring fails with error 3185, Can't find master key from keyring. The keyring_file plugin was removed in MySQL 8.4; load component_keyring_file instead, through a mysqld.my manifest beside mysqld containing {"components": "file://component_keyring_file"} and a component_keyring_file.cnf in the plugin directory giving path and read_only. In a mysql:9.7 test container the first start left an empty key file and the component Disabled; deleting the file and running ALTER INSTANCE RELOAD KEYRING fixed it. grep then found a test token in a plain table's .ibd file but not in the encrypted one. Back up the keyring, or the encrypted tables are lost with it.