CREATE TABLE

Define a table's name, columns, types and constraints — the statement every other statement in the schema depends on.

SyntaxCREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (create_definition, ...) [table_options]

CREATE TABLE is how a table comes into existence. It fixes the table name, the ordered list of columns, the data type of each column, and the constraints the server will enforce on every row from that moment on. In MySQL 8.4 a new table uses the InnoDB storage engine and the utf8mb4 character set unless you override them.

The shape of the statement

Inside the parentheses, each item is either a column definition — a name, a data type, then optional attributes such as NOT NULL, DEFAULT, AUTO_INCREMENT and COMMENT — or a table-level key or constraint: PRIMARY KEY, UNIQUE, INDEX, FOREIGN KEY, CHECK. Options after the closing parenthesis (ENGINE, CHARSET, AUTO_INCREMENT) apply to the table as a whole.

Shorthand and longhand

Several spellings mean exactly the same thing, and you will meet both in other people's schemas.

ShorthandLonghand
id INT PRIMARY KEYid INT NOT NULL, PRIMARY KEY (id)
KEY (col)INDEX (col)
INT, DECINTEGER, DECIMAL

What trips people up

A row has a hard limit of 65,535 bytes shared across all columns, so a table of wide VARCHARs can be rejected at creation time even though no row is near that size; BLOB and TEXT columns contribute only a few bytes toward the limit because their data is stored away from the row.

Example

Example · sql
CREATE TABLE orders (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_id  BIGINT UNSIGNED NOT NULL,
  reference    VARCHAR(32)     NOT NULL,
  status       ENUM('pending','paid','shipped','cancelled')
                               NOT NULL DEFAULT 'pending',
  total_cents  INT UNSIGNED    NOT NULL DEFAULT 0,  -- minor units, never FLOAT
  notes        TEXT            NULL,
  placed_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP
                                        ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),                                 -- table-level form
  UNIQUE KEY uniq_orders_reference (reference),
  KEY idx_orders_customer (customer_id),            -- KEY == INDEX
  CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers (id)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_0900_ai_ci;

When to use it

  • Create the first migration for a new feature's table.
  • Add a lookup table with a natural key and a unique constraint.
  • Copy an existing table's structure into a staging table before a risky backfill.

More examples

The same table written two ways

Both statements produce an identical table. The longhand form is worth preferring in migrations because it lets you name the constraint (`uniq_tags_name`), and a named constraint is what you reference later in ALTER TABLE ... DROP INDEX or in an error message you have to debug.

Example · sql
-- Shorthand: constraints attached to the column
CREATE TABLE tags (
  id   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL UNIQUE
);

-- Longhand: exactly the same table, constraints declared separately
CREATE TABLE tags (
  id   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(50)  NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uniq_tags_name (name)
);

Copying a structure: LIKE versus AS SELECT

Reach for LIKE when you want a true structural twin (archive tables, blue/green migrations). Reach for AS SELECT when you want a filtered snapshot, and remember to re-declare keys — the silent loss of the primary key on a CREATE TABLE ... SELECT is a common production surprise.

Example · sql
-- Empty copy: same columns, attributes and indexes. No data.
-- Foreign key definitions are NOT copied.
CREATE TABLE orders_archive LIKE orders;

-- Copy structure AND rows. Retained attributes are NULL/NOT NULL and,
-- where present, CHARACTER SET, COLLATION, COMMENT and DEFAULT - but
-- no primary key, no indexes and no AUTO_INCREMENT unless you spell
-- them out.
CREATE TABLE orders_2025 (
  PRIMARY KEY (id)            -- add back what SELECT would not carry over
) AS SELECT * FROM orders WHERE placed_at >= '2025-01-01';

A session-scoped temporary table

A TEMPORARY table is visible only within the session that created it and is dropped when that session closes. Use it to stage an intermediate result set that several later queries in the same job need, instead of recomputing an expensive subquery each time.

Example · sql
CREATE TEMPORARY TABLE tmp_active_customer_ids (
  customer_id BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (customer_id)
) ENGINE = InnoDB;

INSERT INTO tmp_active_customer_ids (customer_id)
SELECT DISTINCT customer_id FROM orders WHERE placed_at >= CURRENT_DATE - INTERVAL 30 DAY;

-- Visible only to this connection; dropped automatically when it closes.
SELECT c.* FROM customers c JOIN tmp_active_customer_ids t ON t.customer_id = c.id;

Related

sql data typessql primary keysql not nullsql foreign keysql alter table

Discussion

  • Be the first to comment on this lesson.