DDL

DDL is not undone by a rollback. CREATE TABLE and ALTER TABLE ADD COLUMN were both measured surviving one, so wrapping a migration in a transaction does not give you a way back out of it. Plan a schema change as a sequence you can finish or repeat rather than as something you can abandon halfway, and take a copy of anything you cannot lose.

CREATE TABLE

CREATE TABLE [IF NOT EXISTS] name (
    column_definition | table_constraint [, ...]
);

A column is a name, a type and any number of constraints:

CREATE TABLE Users (
    Id        BIGINT PRIMARY KEY AUTOINCREMENT,
    Username  VARCHAR(100) NOT NULL UNIQUE,
    Email     VARCHAR(255) NOT NULL,
    Age       TINYINT CHECK (Age >= 0 AND Age <= 150),
    IsActive  BOOLEAN DEFAULT TRUE,
    CreatedAt DATETIME DEFAULT NOW()
);

Constraints spanning several columns go at the table level, and can be named:

CREATE TABLE OrderItems (
    OrderId   BIGINT NOT NULL,
    ProductId BIGINT NOT NULL,
    Quantity  INT NOT NULL,

    CONSTRAINT PK_OrderItems PRIMARY KEY (OrderId, ProductId),
    CONSTRAINT FK_OrderItems_Order FOREIGN KEY (OrderId)
        REFERENCES Orders (Id) ON DELETE CASCADE,
    CONSTRAINT CK_OrderItems_Quantity CHECK (Quantity > 0)
);

Naming them is worth doing: a named constraint appears in INFORMATION_SCHEMA.TABLE_CONSTRAINTS and can be dropped by name. An anonymous one is enforced just the same and cannot be referred to afterwards.

Referential actions are NO ACTION, RESTRICT, CASCADE, SET NULL and SET DEFAULT. A cascade fires the child table's triggers as it goes, and a trigger that cancels a cascaded row fails the statement rather than skipping the row, since skipping would leave a child pointing at a key that no longer exists.

Computed columns

CREATE TABLE Orders (
    Id       BIGINT PRIMARY KEY AUTOINCREMENT,
    Subtotal DECIMAL(10,2) NOT NULL,
    Tax      DECIMAL(10,2) NOT NULL,
    Total    AS (Subtotal + Tax) STORED
);

STORED computes on write and keeps the result. VIRTUAL computes on read. A computed column that cannot be evaluated raises an error naming the table, the column and the cause, rather than answering NULL.

ALTER TABLE

ALTER TABLE Users ADD COLUMN LastLoginAt DATETIME;
ALTER TABLE Users DROP COLUMN Age;
ALTER TABLE Users RENAME TO Accounts;
ALTER TABLE Users RENAME COLUMN Username TO Login;

ALTER TABLE Users ALTER COLUMN Email SET NOT NULL;
ALTER TABLE Users ALTER COLUMN Email DROP NOT NULL;
ALTER TABLE Users ALTER COLUMN Status SET DEFAULT 'active';
ALTER TABLE Users ALTER COLUMN Status DROP DEFAULT;
ALTER TABLE Users ALTER COLUMN Age TYPE SMALLINT;

ALTER TABLE Users ADD CONSTRAINT CK_Users_Age CHECK (Age >= 0);
ALTER TABLE Users DROP CONSTRAINT CK_Users_Age;

A primary key cannot be added to an existing table, whether through ADD CONSTRAINT or by adding a column declared PRIMARY KEY. A key is a property of the table: it needs the key list rewritten and every existing row checked. Both forms are refused with a message saying so rather than half-applied. Rebuild the table instead: create the new one, copy the rows, drop the old one, rename.

A NOT NULL column added to a table that already has rows needs a DEFAULT. Without one the statement is refused, and the message says a default would work. The refusal is worth having: the statement used to be accepted, leave NULL in every existing row, and then refuse every later write to that table including an update of an unrelated column, with no way back short of a rebuild. On an empty table the same statement is accepted.

A constraint added with ADD CONSTRAINT has to be named. ALTER TABLE t ADD PRIMARY KEY (c) answers that a name is required.

Only two properties of an existing column can be changed in place, its DEFAULT and its NOT NULL. A type change is ALTER COLUMN ... TYPE, which rewrites every row and refuses a value that will not read as the new type, naming the value, before it writes anything.

CREATE INDEX

CREATE [UNIQUE] INDEX [IF NOT EXISTS] name ON table (column [ASC|DESC], ...)
    [INCLUDE (column, ...)]
    [WHERE condition];

CREATE INDEX IX_Orders_Customer ON Orders (CustomerId);
CREATE UNIQUE INDEX IX_Users_Email ON Users (Email);
CREATE INDEX IX_Orders_Recent ON Orders (CreatedAt DESC);
CREATE INDEX IX_Users_EmailLower ON Users (LOWER(Email));
CREATE INDEX IX_Orders_Covering ON Orders (CustomerId) INCLUDE (Total, Status);
CREATE INDEX IX_Orders_Pending ON Orders (CustomerId) WHERE Status = 'pending';

An index cannot be built on an expression whose value moves, so RANDOM(), NOW() and subqueries are refused. An index key is computed once when the row is written and never recomputed, so an expression that changes would produce an index that quietly disagrees with the table.

Note that a WHERE index is maintained but never selected automatically by the planner; see indexing.

CREATE VIEW

CREATE VIEW [IF NOT EXISTS] name [(column, ...)] AS query;

CREATE VIEW ActiveUsers AS
    SELECT Id, Username, Email FROM Users WHERE IsActive = TRUE;

Views are expanded when queried rather than materialised.

CREATE TRIGGER

CREATE TRIGGER [IF NOT EXISTS] name
    {BEFORE | AFTER | INSTEAD OF} {INSERT | UPDATE [OF column, ...] | DELETE}
    ON table
    [FOR EACH ROW]
    [WHEN (condition)]
BEGIN
    statements
END;

CREATE TRIGGER TR_Orders_Audit
    AFTER UPDATE ON Orders
    FOR EACH ROW
BEGIN
    INSERT INTO OrdersAudit (OrderId, OldStatus, NewStatus, At)
    VALUES (OLD.Id, OLD.Status, NEW.Status, NOW());
END;

OLD.column and NEW.column are readable. SIGNAL raises an error from inside a trigger, which is how a BEFORE trigger refuses a write.

Assigning to NEW does not parse. SET NEW.UpdatedAt = NOW() in a BEFORE trigger, which is the idiom BEFORE triggers largely exist for, is unavailable. Set the value in the statement instead, or use a computed column.

A trigger body may contain only SELECT, INSERT, UPDATE, DELETE and MERGE. No DDL, no transaction control, no CALL. A trigger runs inside a loop over rows, and DDL against the object that loop is walking is not survivable: a DROP TABLE fired from a trigger on the table being written would report success and destroy it.

Statements nest at most 32 deep, so a trigger that writes to its own table stops with a catchable error rather than exhausting the stack.

Sequences

CREATE SEQUENCE OrderNumbers START WITH 1000 INCREMENT BY 1;
ALTER SEQUENCE OrderNumbers RESTART WITH 5000;
DROP SEQUENCE OrderNumbers;

Dropping things

DROP TABLE    [IF EXISTS] name;
DROP INDEX    [IF EXISTS] name;      -- the index name alone, not "ON table"
DROP VIEW     [IF EXISTS] name;
DROP TRIGGER  [IF EXISTS] name;
DROP SEQUENCE [IF EXISTS] name;

TRUNCATE TABLE name;

A note on expressions in schema

A CHECK, a DEFAULT, a computed column or an index expression naming a function the engine does not have is refused when it is written, rather than accepted and failing later against real data.

Where to go next

• Types, what goes in a column definition
• DML, putting rows in
• Indexing, which indexes the planner will use
• Routines, CREATE FUNCTION and CREATE PROCEDURE