Skip to content
Tutorials Center
My learning

Topics

Lesson 14 of 75 ยท Tables, Constraints and Schema Design

CREATE TABLE in Depth

Master MySQL CREATE TABLE: column options, DEFAULT expressions, AUTO_INCREMENT, COMMENT, ENGINE and CHARSET table options, LIKE, AS SELECT and temporary tables.

You created your first table in Your First Database and Table, and you chose column types in the data types chapter. CREATE TABLE can do much more than list columns. It sets defaults, numbers rows automatically, documents columns, picks a storage engine and a character set, and can even copy the shape or the contents of another table.

This lesson walks through the full statement as MySQL 8.4 understands it. The portable core runs live here in the SQLite stand-in. MySQL-only options such as ENGINE, ROW_FORMAT and CREATE TABLE ... LIKE are shown as read-only blocks, and each one is marked.

Three layers of CREATE TABLE

Column options Type, NOT NULL, DEFAULT, AUTO_INCREMENT, COMMENT.
Table elements Keys, indexes and constraints.
Table options ENGINE, CHARSET, COLLATE, ROW_FORMAT.

Each layer is optional except the column list.

1 / 2

The anatomy of a statement

A full MySQL statement has three parts: the column definitions, the table-level elements (keys, indexes, constraints) and the table options after the closing parenthesis.

SQL
CREATE TABLE tickets (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  title       VARCHAR(200) NOT NULL COMMENT 'Short summary shown in lists',
  status      ENUM('open','done') NOT NULL DEFAULT 'open',
  sla_hours   SMALLINT UNSIGNED NOT NULL DEFAULT (24 * 3),
  created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
              ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_tickets_status (status)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_0900_ai_ci
  COMMENT='Support tickets';

This block is read-only because ENUM, UNSIGNED, KEY, ON UPDATE and the table options are MySQL syntax that SQLite rejects or ignores. Everything in it is explained below.

Column options

After the type, a column can carry any of these options, in almost any order.

  • NOT NULL or NULL. Without either, the column accepts NULL. Make NOT NULL your habit and allow NULL only when "unknown" is a real state.
  • DEFAULT value. The value used when an INSERT leaves the column out.
  • AUTO_INCREMENT. Hands out the next number automatically. A table may have only one such column, and it must be indexed (usually it is the primary key). The ID strategies lesson covers its behavior in depth.
  • COMMENT 'text'. Documentation stored in the data dictionary. It shows up in SHOW CREATE TABLE, in information_schema.COLUMNS and in most GUI tools. Comments cost nothing and save the next reader a guess.
  • UNIQUE and PRIMARY KEY as shorthand constraints, covered in Keys and Constraints.
  • COLLATE and CHARACTER SET for one column, as discussed in Character Sets and Collations.
  • INVISIBLE, GENERATED ALWAYS AS (...) and CHECK (...), covered in the lessons on generated columns and constraints.

DEFAULT values and expressions

Until MySQL 8.0.13 a default had to be a literal, with CURRENT_TIMESTAMP as the single exception for TIMESTAMP and DATETIME. Since 8.0.13 you can write an expression as a default, as long as it is wrapped in its own parentheses. That is why DEFAULT (24 * 3) is valid and DEFAULT 24 * 3 is a syntax error.

SQL
-- MySQL 8.0.13 and later (read-only here)
CREATE TABLE invites (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  token      CHAR(36)  NOT NULL DEFAULT (UUID()),
  expires_on DATE      NOT NULL DEFAULT (CURRENT_DATE + INTERVAL 7 DAY),
  payload    JSON      NOT NULL DEFAULT (JSON_OBJECT())
);

Expression defaults are the only way to give a JSON, BLOB or TEXT column a default, because those types reject literal defaults. An expression default may use other columns of the same row, but it may not use subqueries, stored functions or AUTO_INCREMENT columns.

An expression default that calls a nondeterministic function such as UUID() or RAND() is evaluated for every inserted row. That is what you want for a token, but it makes statement-based replication unsafe. Production servers use row-based replication, where this is not a problem.

The next example runs live. SQLite also requires parentheses around expression defaults, and it accepts only constant expressions, so DEFAULT (24 * 3) works in both. The last column of the result shows that CURRENT_TIMESTAMP filled itself in without you providing a value. We print only whether it is set, because the actual time changes on every run.

SQL Defaults fill in what you leave out

Press Run (or Ctrl + Enter) to see the result here.

All three rows get sla_hours = 72. Row 1 uses every default, row 2 overrides status and priority, and row 3 shows that writing NULL for a column without a default is fine when the column allows it. Note that NULL is not the same as "use the default". To get a default explicitly in MySQL you write the keyword DEFAULT in the values list, for example VALUES ('Fix login', DEFAULT).

Automatic timestamps

Almost every table benefits from created_at and updated_at. In MySQL you can keep both current without touching your application code:

SQL
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

ON UPDATE CURRENT_TIMESTAMP rewrites the column whenever the row changes and any other column actually gets a new value. The same clauses work on DATETIME. SQLite has no ON UPDATE column option, so this is read-only; in the live examples you set updated_at yourself.

Table options

Table options come after the closing parenthesis, separated by spaces or commas.

Option Meaning Usual value
ENGINE The storage engine InnoDB (the default, and the right choice)
DEFAULT CHARSET Character set for text columns that do not say otherwise utf8mb4
COLLATE Comparison and sort rules for those columns utf8mb4_0900_ai_ci (default in 8.x)
ROW_FORMAT How InnoDB lays rows out on a page DYNAMIC (the default)
AUTO_INCREMENT The next number to hand out for example 1000 to start at 1000
COMMENT Free text for the table a short description

You rarely need to write them. On a modern server, the schema's defaults already give you InnoDB, utf8mb4 and DYNAMIC rows. Writing the first three explicitly is still a good habit for scripts that must produce the same table on every server, such as migrations that run on a laptop and on a managed cloud database whose defaults you did not choose.

Avoid ROW_FORMAT=COMPACT and REDUNDANT. They are old formats that store long column values inside the row and limit index key length to 767 bytes. DYNAMIC keeps big values off-page and allows index keys up to 3072 bytes.

Copying a table: LIKE and AS SELECT

Two shortcuts create a table from an existing one, and they do very different things.

CREATE TABLE ... LIKE copies the definition: columns, defaults, indexes and the storage engine. It copies no rows and no foreign keys.

SQL
CREATE TABLE tickets_archive LIKE tickets;   -- MySQL only, read-only here
INSERT INTO tickets_archive SELECT * FROM tickets WHERE status = 'done';

This is the usual way to create an archive table or a scratch copy for a test, because the copy has the same indexes as the original.

CREATE TABLE ... AS SELECT (often called CTAS) creates a table from a query and fills it in one step. The column types come from the query's result. It does not copy indexes or keys, so you usually add them afterwards.

SQL
CREATE TABLE paid_orders AS
SELECT id, customer, total FROM orders WHERE status = 'paid';

CTAS works in SQLite too, so you can run it. Here we also inspect what the new table looks like, which shows how little of the original definition survives:

SQL CREATE TABLE ... AS SELECT and a temporary table

Press Run (or Ctrl + Enter) to see the result here.

The sql column shows CREATE TABLE paid_orders(id INT,customer TEXT,total REAL). The primary key and the NOT NULL rules are gone. In MySQL the column types and nullability carry over, but the keys and indexes do not, so the lesson is the same: after CTAS, add the primary key and indexes yourself.

Temporary tables

A temporary table is visible only to the connection that created it and disappears when that connection ends. Two clients can each create a temporary table with the same name without clashing. Use one to hold intermediate results in a long script or a stored procedure.

SQL
CREATE TEMPORARY TABLE spend (
  customer VARCHAR(100) NOT NULL,
  spent    DECIMAL(10,2) NOT NULL
) ENGINE=InnoDB;

DROP TEMPORARY TABLE spend;

Things to know:

  • SHOW TABLES does not list temporary tables, and they have no entry in information_schema.TABLES.
  • A temporary table hides a permanent table of the same name for your session. Dropping it with plain DROP TABLE drops the temporary one first, which is why DROP TEMPORARY TABLE is the safer spelling.
  • You cannot refer to the same temporary table twice in one query (for example a self-join). Copy it to a second temporary table, or use a CTE.
  • Temporary tables are not replicated as data. They exist only on the server where the session ran.
  • Do not confuse them with the internal temporary tables MySQL builds on its own for sorting and grouping. You will meet those in the EXPLAIN lesson.

Reading a table's definition back

Use SHOW CREATE TABLE tickets; to see the statement MySQL would use to recreate the table, including defaults it filled in and the options it chose. Use DESCRIBE tickets; for a compact column list. Both are read-only here, but SQLite has an equivalent for the live runner: SELECT sql FROM sqlite_master WHERE name = 'tickets';, as in the last example.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which default is valid in MySQL 8.4 for a JSON column?

    Expression defaults must be wrapped in parentheses, and JSON, BLOB and TEXT columns accept only expression defaults, not literals.
  2. 2. What does CREATE TABLE copy AS SELECT * FROM original NOT carry over reliably?

    CTAS copies data and column types from the query result, but you must recreate the primary key and secondary indexes yourself.
  3. 3. How is CREATE TABLE copy LIKE original different from CTAS?

    LIKE clones the definition (columns, defaults, indexes, engine) and leaves the new table empty. Foreign keys are not copied.
  4. 4. What happens to a temporary table when the connection that created it closes?

    Temporary tables are private to the session and are removed when it ends.

Summary

  • CREATE TABLE has three layers: column definitions, table elements such as keys, and table options such as ENGINE and DEFAULT CHARSET.
  • Since 8.0.13 a default can be an expression in parentheses, for example DEFAULT (UUID()). This is also the only way to default JSON, BLOB and TEXT columns.
  • DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP keep audit columns current without application code.
  • LIKE copies a table's structure, AS SELECT copies a query's rows and types, and neither is a complete clone on its own.
  • Temporary tables are private to a session, vanish at disconnect, and can hide a permanent table of the same name.
  • COMMENT on columns and tables is free documentation. SHOW CREATE TABLE reads the whole definition back.

Next: Keys and Constraints, where you make the database refuse bad data.