Creating and Altering Tables

Article · 2:00

CREATE TABLE in full, what ALTER costs on a big table, and the difference between DROP, DELETE and TRUNCATE.

A table definition is a list of columns with their types, plus the rules that apply to the table as a whole: the primary key, the unique keys, the foreign keys, the indexes, the engine and the character set. Writing all of it at creation is cheaper than adding it later, because ALTER TABLE on a large table can take a long time and, on older versions, locks it throughout.

MySQL 8 does many alterations online - adding a column at the end, adding an index - so reads and writes continue. Others still rebuild the whole table: changing a column's type, reordering columns, changing the engine. On a hundred-million-row table that is hours, and the time to discover which kind you are about to run is before you run it, on a copy.

Three statements remove data and they are not interchangeable. DELETE removes rows, can be filtered with WHERE, fires triggers, and can be rolled back. TRUNCATE empties the table by recreating it - fast, cannot be filtered, resets AUTO_INCREMENT, and is DDL, so it commits. DROP removes the table itself.

SHOW CREATE TABLE is the statement to remember. It prints the definition as the server actually holds it, which is the only reliable answer to "what is this table really".

CREATE TABLE orders (
  id           INT UNSIGNED  PRIMARY KEY AUTO_INCREMENT,
  customer_id  INT UNSIGNED  NOT NULL,
  reference    CHAR(10)      NOT NULL,
  total        DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  status       ENUM('pending','paid','shipped','cancelled')
                             NOT NULL DEFAULT 'pending',
  placed_at    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,

  -- Declared here rather than added later: an ALTER on a large table may
  -- rebuild the whole thing.
  UNIQUE KEY uq_orders_reference (reference),
  KEY idx_orders_customer_placed (customer_id, placed_at),
  CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers (id)
    ON DELETE RESTRICT
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

-- The truth about a table, as the server holds it.
SHOW CREATE TABLE orders;
DESCRIBE orders;

-- Online in MySQL 8: reads and writes continue.
ALTER TABLE orders ADD COLUMN notes VARCHAR(500) NULL AFTER status;

-- Rebuilds the table. Time it on a copy first.
-- ALTER TABLE orders MODIFY COLUMN reference VARCHAR(20) NOT NULL;

DELETE FROM orders WHERE status = 'cancelled';  -- filtered, triggers, rollback
-- TRUNCATE TABLE orders;   -- all rows, resets AUTO_INCREMENT, commits
-- DROP TABLE orders;       -- the table itself