DDL — Schema Definition Language

DDL statements define and modify the structure of the database: tables, columns, constraints, and indexes. All DDL operations are transactional in AxiomDB — a failed DDL statement is automatically rolled back.

CREATE DATABASE

Creates a new logical database in the persisted catalog.

Syntax

CREATE DATABASE database_name;

Example

CREATE DATABASE analytics;
SHOW DATABASES;

Expected output includes:

CREATE DATABASE fails if the name already exists:

CREATE DATABASE analytics;
-- ERROR 1007 (HY000): Can't create database 'analytics'; database exists

DROP DATABASE

Removes a logical database from the catalog.

Syntax

DROP DATABASE database_name;
DROP DATABASE IF EXISTS database_name;

Behavior

• Removing a database also removes the tables it owns from SQL/catalog lookup.
• IF EXISTS suppresses the error for a missing database.
• The current connection cannot drop the database it has selected with USE.

DROP DATABASE analytics;

DROP DATABASE IF EXISTS scratch;

USE analytics;
DROP DATABASE analytics;
-- ERROR 1105 (HY000): Can't drop database 'analytics'; database is currently selected

CREATE TABLE

Basic Syntax

CREATE TABLE [IF NOT EXISTS] table_name (
    column_name  data_type  [column_constraints...],
    ...
    [table_constraints...]
);

Column Constraints

NOT NULL

Rejects any attempt to insert or update a row with a NULL value in this column.

CREATE TABLE employees (
    id    BIGINT NOT NULL,
    name  TEXT   NOT NULL,
    dept  TEXT            -- nullable: dept may be unassigned
);

DEFAULT

Provides a value when the column is omitted from INSERT.

CREATE TABLE orders (
    id         BIGINT   PRIMARY KEY AUTO_INCREMENT,
    status     TEXT     NOT NULL DEFAULT 'pending',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    priority   INT      NOT NULL DEFAULT 0
);

-- Default values are used automatically
INSERT INTO orders (status) VALUES ('shipped');
-- Row: id=<auto>, status='shipped', created_at=<now>, priority=0

PRIMARY KEY

Declares a column (or set of columns) as the primary key. A primary key:

• Implies NOT NULL
• Creates a unique B+ Tree index automatically
• Is used for REFERENCES in foreign keys

-- Single-column primary key
CREATE TABLE users (
    id   BIGINT PRIMARY KEY AUTO_INCREMENT,
    name TEXT   NOT NULL
);

-- Composite primary key (declared as table constraint)
CREATE TABLE order_items (
    order_id   BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity   INT    NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

UNIQUE

Guarantees no two rows share the same value in this column (or set of columns). NULL values are excluded from uniqueness checks — multiple NULLs are allowed.

CREATE TABLE accounts (
    id       BIGINT PRIMARY KEY AUTO_INCREMENT,
    email    TEXT   NOT NULL UNIQUE,
    username TEXT   NOT NULL UNIQUE
);

AUTO_INCREMENT / SERIAL

Automatically generates a monotonically increasing integer for each new row. The counter starts at 1 and increments by 1 for each inserted row. The following forms are all equivalent:

-- MySQL-style
id BIGINT PRIMARY KEY AUTO_INCREMENT

-- PostgreSQL-style shorthand (SERIAL = INT AUTO_INCREMENT, BIGSERIAL = BIGINT AUTO_INCREMENT)
id SERIAL    PRIMARY KEY
id BIGSERIAL PRIMARY KEY

Behavior:

CREATE TABLE users (
    id   BIGINT PRIMARY KEY AUTO_INCREMENT,
    name TEXT   NOT NULL
);

-- Omit the AUTO_INCREMENT column — the engine generates the value
INSERT INTO users (name) VALUES ('Alice');   -- id = 1
INSERT INTO users (name) VALUES ('Bob');     -- id = 2

-- Retrieve the last generated ID (current session only)
SELECT LAST_INSERT_ID();   -- returns 2
SELECT lastval();          -- PostgreSQL alias — same result

-- Multi-row INSERT: LAST_INSERT_ID() returns the ID of the FIRST row in the batch
INSERT INTO users (name) VALUES ('Carol'), ('Dave');  -- ids: 3, 4
SELECT LAST_INSERT_ID();   -- returns 3

-- Explicit non-NULL value bypasses the sequence and does NOT advance it
INSERT INTO users (id, name) VALUES (100, 'Eve');
-- id=100; sequence remains at 4; next auto id will be 5

LAST_INSERT_ID() returns 0 if no auto-increment INSERT has been performed in the current session. See LAST_INSERT_ID() in expressions for the full function reference.

TRUNCATE resets the counter:

TRUNCATE TABLE users;
INSERT INTO users (name) VALUES ('Frank');  -- id = 1 (reset by TRUNCATE)

REFERENCES — Foreign Keys

Declares a foreign key relationship to another table’s primary key.

CREATE TABLE orders (
    id         BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id    BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    product_id BIGINT NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
    placed_at  TIMESTAMP NOT NULL
);

ON DELETE actions:

ON UPDATE actions: Same options as ON DELETE — apply when the referenced primary key is updated.

Current limitation: Only ON UPDATE RESTRICT (the default) is enforced. ON UPDATE CASCADE and ON UPDATE SET NULL return NotImplemented and are planned for Phase 6.10. Write ON UPDATE RESTRICT or omit the clause entirely for correct behaviour today.

CREATE TABLE order_items (
    id         BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_id   BIGINT NOT NULL
        REFERENCES orders(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    product_id BIGINT NOT NULL
        REFERENCES products(id)
        ON DELETE RESTRICT
        ON UPDATE RESTRICT,
    quantity   INT    NOT NULL,
    unit_price DECIMAL NOT NULL
);

CHECK

Validates that a condition is TRUE for every row. A row where the CHECK condition evaluates to FALSE or NULL is rejected.

CREATE TABLE products (
    id     BIGINT  PRIMARY KEY AUTO_INCREMENT,
    name   TEXT    NOT NULL,
    price  DECIMAL NOT NULL CHECK (price > 0),
    stock  INT     NOT NULL CHECK (stock >= 0),
    rating REAL    CHECK (rating IS NULL OR (rating >= 1.0 AND rating <= 5.0))
);

Table-Level Constraints

Table constraints apply to multiple columns and are declared after all column definitions.

CREATE TABLE shipments (
    id           BIGINT    PRIMARY KEY AUTO_INCREMENT,
    order_id     BIGINT    NOT NULL,
    warehouse_id INT       NOT NULL,
    shipped_at   TIMESTAMP,
    delivered_at TIMESTAMP,

    -- Named constraints (recommended for meaningful error messages)
    CONSTRAINT fk_shipment_order
        FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,

    CONSTRAINT chk_delivery_after_shipment
        CHECK (delivered_at IS NULL OR delivered_at >= shipped_at),

    CONSTRAINT uq_one_active_shipment
        UNIQUE (order_id, warehouse_id)
);

IF NOT EXISTS

Suppresses the error when the table already exists. Useful in migration scripts.

CREATE TABLE IF NOT EXISTS config (
    key   TEXT NOT NULL UNIQUE,
    value TEXT NOT NULL
);

Full Example — E-commerce Schema

CREATE TABLE users (
    id         BIGINT      PRIMARY KEY AUTO_INCREMENT,
    email      TEXT        NOT NULL UNIQUE,
    name       TEXT        NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMPTZ
);

CREATE TABLE categories (
    id   INT  PRIMARY KEY AUTO_INCREMENT,
    name TEXT NOT NULL UNIQUE
);

CREATE TABLE products (
    id          BIGINT      PRIMARY KEY AUTO_INCREMENT,
    category_id INT         NOT NULL REFERENCES categories(id),
    name        TEXT        NOT NULL,
    description TEXT,
    price       DECIMAL     NOT NULL CHECK (price > 0),
    stock       INT         NOT NULL DEFAULT 0 CHECK (stock >= 0),
    created_at  TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    id          BIGINT      PRIMARY KEY AUTO_INCREMENT,
    user_id     BIGINT      NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    total       DECIMAL     NOT NULL CHECK (total >= 0),
    status      TEXT        NOT NULL DEFAULT 'pending',
    placed_at   TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    shipped_at  TIMESTAMPTZ,
    CONSTRAINT chk_order_status CHECK (
        status IN ('pending', 'paid', 'shipped', 'delivered', 'cancelled')
    )
);

CREATE TABLE order_items (
    order_id   BIGINT  NOT NULL REFERENCES orders(id)   ON DELETE CASCADE,
    product_id BIGINT  NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
    quantity   INT     NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL NOT NULL CHECK (unit_price > 0),
    PRIMARY KEY (order_id, product_id)
);

CREATE TABLE LIKE

Copies the schema of an existing table into a new empty table. Column definitions, constraints, and indexes are all replicated — but no rows are copied.

CREATE TABLE staging LIKE production_orders;

CREATE TABLE IF NOT EXISTS archive_users LIKE users;

The new table is independent of the source. Changes to the source schema (via ALTER TABLE) do not affect the copy, and vice versa.

What is copied:

• All columns (name, type, nullability, default, auto_increment)
• All constraints (PRIMARY KEY, UNIQUE, CHECK, FOREIGN KEY)
• All secondary indexes (with fresh empty B-tree roots)

What is not copied: rows, AUTO_INCREMENT counter state, table-level comments.

CREATE TABLE AS SELECT (CTAS)

Creates a new heap table and populates it from a SELECT query in one statement.

CREATE TABLE cheap_products AS
SELECT id, name, price
FROM products
WHERE price < 50;

-- With AS keyword (optional)
CREATE TABLE report_2024 AS
SELECT user_id, SUM(total) AS revenue
FROM orders
WHERE YEAR(placed_at) = 2024
GROUP BY user_id;

Column types are inferred from the first non-NULL value in each column of the result set. If the first row is all-NULL for a column, AxiomDB falls back to TEXT. Column names come from the SELECT projection aliases (or the raw expression text if no alias is given).

The resulting table is always a heap table regardless of whether the source table is clustered. To create a clustered copy, add a PRIMARY KEY and use ALTER TABLE ... REBUILD or declare the PK inline:

-- Heap CTAS first, then promote to clustered
CREATE TABLE copy AS SELECT * FROM src;
ALTER TABLE copy ADD CONSTRAINT PRIMARY KEY (id);

CREATE INDEX

Indexes accelerate lookups and range scans. AxiomDB automatically creates a unique B+ Tree index for every PRIMARY KEY and UNIQUE constraint. Additional indexes are created explicitly. CREATE INDEX works on both heap tables and clustered (PRIMARY KEY) tables.

Basic Syntax

CREATE [UNIQUE] INDEX [IF NOT EXISTS] index_name
ON table_name (column [ASC|DESC], ...)
[WITH (fillfactor = N)]
[WHERE condition];

fillfactor controls how full a B-Tree leaf page gets before splitting (10–100, default 90). Lower values leave room for future inserts without triggering splits. See Fill Factor for details.

Examples

-- Standard index
CREATE INDEX idx_users_email ON users (email);

-- Composite index: queries filtering by (user_id, placed_at) benefit
CREATE INDEX idx_orders_user_date ON orders (user_id, placed_at DESC);

-- Unique index (equivalent to UNIQUE column constraint)
CREATE UNIQUE INDEX uq_products_sku ON products (sku);

-- Partial index: index only active products (reduces index size)
CREATE INDEX idx_active_products ON products (category_id)
WHERE deleted_at IS NULL;

-- Fill factor: append-heavy time-series table (leaves 30% free for inserts)
CREATE INDEX idx_ts ON events(created_at) WITH (fillfactor = 70);

-- Fill factor + partial index combined
CREATE UNIQUE INDEX uq_active_email ON users(email)
WHERE deleted_at IS NULL
-- WITH clause can appear before or after WHERE (both are accepted)

When to Add an Index

• Columns appearing in WHERE, JOIN ON, or ORDER BY clauses on large tables
• Foreign key columns (AxiomDB does not auto-index FK columns — add them explicitly)
• Columns used in range queries (BETWEEN, >, <)

See Indexes for the query planner interaction and composite index column ordering rules.

DROP TABLE

Removes a table and all its data permanently.

DROP TABLE [IF EXISTS] table_name [CASCADE | RESTRICT];

-- Safe drop: fails if referenced by other tables
DROP TABLE products;

-- Drop without error if already gone
DROP TABLE IF EXISTS temp_import;

-- Drop even if referenced (removes FK constraints first)
DROP TABLE categories CASCADE;

Dropping a table is immediate and permanent. There is no RECYCLE BIN. Make sure you have a backup or are inside a transaction if you need to recover.

DROP INDEX

Removes an index. The table and its data are not affected.

DROP INDEX [IF EXISTS] index_name;

DROP INDEX idx_users_email;
DROP INDEX IF EXISTS idx_old_lookup;

ALTER TABLE

Modifies the structure of an existing table. All four forms are blocking operations — no concurrent DDL is allowed while an ALTER TABLE is in progress.

Add Column

Adds a new column at the end of the column list. If existing rows are present, they are rewritten to include the default value for the new column. If no DEFAULT clause is given, existing rows receive NULL for that column.

ALTER TABLE table_name ADD COLUMN column_name data_type [NOT NULL] [DEFAULT expr];

-- Add a nullable column (existing rows get NULL)
ALTER TABLE users ADD COLUMN phone TEXT;

-- Add a NOT NULL column with a default (existing rows get 0)
ALTER TABLE orders ADD COLUMN priority INT NOT NULL DEFAULT 0;

-- Add a column with a string default
ALTER TABLE products ADD COLUMN status TEXT NOT NULL DEFAULT 'active';

A column with NOT NULL and no DEFAULT cannot be added to a non-empty table — existing rows would have no value to fill in and would violate the constraint. Provide a DEFAULT value, or add the column as nullable first and back-fill the data before adding the constraint.

Drop Column

Removes a column from the table. All existing rows are rewritten without the dropped column’s value. The column name must exist unless IF EXISTS is used.

ALTER TABLE table_name DROP COLUMN column_name [IF EXISTS];

-- Remove a column (fails if the column does not exist)
ALTER TABLE users DROP COLUMN phone;

-- Remove a column only if it exists (idempotent, safe in migrations)
ALTER TABLE users DROP COLUMN phone IF EXISTS;

Dropping a column is permanent. The data stored in that column is discarded when rows are rewritten and cannot be recovered without a backup.

Dropping a column that is part of a UNIQUE index or a FOREIGN KEY is rejected with an error. Drop the index or constraint first, then drop the column. Dropping a PRIMARY KEY column is not allowed on clustered tables (the PK is the physical storage key).

Modify Column

Changes the data type or nullability of an existing column. All existing rows are rewritten, coercing their stored values to the new type.

ALTER TABLE table_name MODIFY COLUMN column_name new_type [NOT NULL];

-- Widen an integer column to 64 bits (existing values preserved)
ALTER TABLE events MODIFY COLUMN count BIGINT;

-- Convert integers to text (always safe, values become their decimal string)
ALTER TABLE codes MODIFY COLUMN code TEXT;

-- Add a NOT NULL constraint (fails if any row has NULL in that column)
ALTER TABLE orders MODIFY COLUMN status TEXT NOT NULL;

Rules and restrictions:

• Narrowing casts (e.g. BIGINT → INT, TEXT → INT) are applied with strict coercion. If any existing value cannot be represented in the new type the statement fails and no rows are changed.
• A column that is part of a secondary index (UNIQUE or otherwise) cannot have its type changed. Drop the index first, modify the column, then recreate the index.
• The PRIMARY KEY column’s type cannot be changed on a clustered table.
• Changing nullability from nullable to NOT NULL is allowed only when every existing row has a non-NULL value for that column.

Rename Column

Renames an existing column. This is a catalog-only operation — no rows are rewritten because the positional encoding is not affected by column names.

ALTER TABLE table_name RENAME COLUMN old_name TO new_name;

-- Rename a column
ALTER TABLE users RENAME COLUMN full_name TO display_name;

-- Rename to fix a typo
ALTER TABLE orders RENAME COLUMN shiped_at TO shipped_at;

Rename Table

Renames the table itself. This is a catalog-only operation.

ALTER TABLE old_name RENAME TO new_name;

-- Rename during a refactoring
ALTER TABLE user_profiles RENAME TO profiles;

-- Rename a staging table after a migration
ALTER TABLE orders_import RENAME TO orders;

Rebuild To Clustered

Migrates a legacy heap table that already has PRIMARY KEY metadata into clustered storage.

ALTER TABLE table_name REBUILD;

Example:

-- After opening an older AxiomDB database where `users` is still heap-backed
ALTER TABLE users REBUILD;

Behavior:

• walks the existing PRIMARY KEY index in logical key order
• rebuilds the table into a clustered PRIMARY KEY tree
• rebuilds every non-primary index so it stores clustered PK bookmarks instead of heap RecordIds
• swaps the catalog metadata atomically at the end of the statement

Common errors:

ALTER TABLE logs REBUILD;
-- ERROR 1105 (HY000): ALTER TABLE REBUILD requires a PRIMARY KEY on 'logs'

ALTER TABLE users REBUILD;
-- ERROR 1105 (HY000): table 'users' is already clustered

Not Yet Supported

The following ALTER TABLE forms are planned for later phases:

• ADD CONSTRAINT / DROP CONSTRAINT for multi-column foreign keys
• Dropping or modifying a column that participates in a secondary index (drop the index first)
• Non-blocking ALTER TABLE (zero-downtime schema migration via shadow table + WAL delta)

TRUNCATE TABLE

Removes all rows from a table without dropping its structure, and resets the AUTO_INCREMENT counter to 1. The table schema, indexes, and constraints are preserved.

TRUNCATE TABLE table_name;

-- Wipe a staging table before re-importing
TRUNCATE TABLE import_staging;

-- AUTO_INCREMENT is always reset after TRUNCATE
CREATE TABLE log_events (id INT AUTO_INCREMENT PRIMARY KEY, msg TEXT);
INSERT INTO log_events (msg) VALUES ('start'), ('end');  -- ids: 1, 2
TRUNCATE TABLE log_events;
INSERT INTO log_events (msg) VALUES ('restart');          -- id: 1

Returns Affected { count: 0 } (MySQL convention). See also TRUNCATE TABLE in the DML reference for a comparison with DELETE FROM table.

ANALYZE

Refreshes per-column statistics used by the query planner to choose between an index scan and a full table scan.

ANALYZE;                          -- all tables in the current schema
ANALYZE TABLE table_name;         -- specific table, all indexed columns
ANALYZE TABLE table_name (col);   -- specific table, one column only

ANALYZE computes exact row_count and NDV (number of distinct non-NULL values) for each target column by scanning the full table. Results are stored in the axiom_stats system catalog and are immediately available to the planner.

-- After a bulk import, refresh stats so the planner uses correct selectivity:
INSERT INTO products SELECT * FROM products_staging;
ANALYZE TABLE products;

-- Check a single column after targeted inserts:
ANALYZE TABLE orders (status);

See Index Statistics for how NDV and row_count affect query planning decisions.

SHOW INDEX

Lists all indexes defined on a table. SHOW INDEXES and SHOW KEYS are synonyms recognized by MySQL clients and ORMs.

SHOW INDEX   FROM table_name;
SHOW INDEXES FROM table_name;
SHOW KEYS    FROM table_name;

The result set matches MySQL’s SHOW INDEX column layout:

CREATE TABLE orders (
    id       INT  PRIMARY KEY,
    email    TEXT UNIQUE,
    status   TEXT
);
CREATE INDEX idx_status ON orders (status);

SHOW INDEX FROM orders;

Example output:

SHOW TABLES / SHOW FULL TABLES

Lists tables in the current (or specified) schema.

SHOW TABLES [FROM schema] [LIKE 'pattern'];
SHOW FULL TABLES [FROM schema] [LIKE 'pattern'];

SHOW FULL TABLES adds a Table_type column, required by Sequelize, ActiveRecord, and other ORMs that probe the schema on startup.

CREATE TABLE products (id INT, name TEXT);
CREATE TABLE orders   (id INT, total REAL);

SHOW FULL TABLES;
-- Tables_in_public  Table_type
-- products          BASE TABLE
-- orders            BASE TABLE

SHOW COLUMNS / SHOW FULL COLUMNS

Lists column metadata for a table. DESCRIBE and DESC are synonyms.

SHOW COLUMNS      FROM table_name;
SHOW FULL COLUMNS FROM table_name;
DESCRIBE table_name;

SHOW FULL COLUMNS adds three extra columns required by Prisma, TypeORM, and MySQL Workbench:

SHOW TABLE STATUS

Returns one row per table with storage metadata, compatible with MySQL’s SHOW TABLE STATUS output.

SHOW TABLE STATUS [FROM schema] [LIKE 'pattern'];

The result set has 18 columns:

SHOW TABLE STATUS LIKE 'order%';
-- Returns rows for all tables whose names start with "order".

SHOW ENGINES / SHOW CHARSET / SHOW COLLATION

Informational commands used by DB management tools (MySQL Workbench, DBeaver, TablePlus) and JDBC drivers on connect.

SHOW ENGINES;
SHOW CHARSET;           -- or SHOW CHARACTER SET
SHOW COLLATION;

SHOW ENGINES returns a single row:

SHOW CHARSET returns four rows: utf8mb4, utf8, latin1, binary.

SHOW COLLATION returns five rows: utf8mb4_general_ci, utf8mb4_bin, utf8_general_ci, latin1_swedish_ci, binary.

SHOW WARNINGS / SHOW ERRORS

Returns the warning or error list from the current session.

SHOW WARNINGS [LIMIT N];
SHOW ERRORS  [LIMIT N];

MySQL connectors (JDBC, MySQL Connector/Python, mysqlclient) issue SHOW WARNINGS automatically after every DML statement. AxiomDB returns a three-column result set that these clients expect:

SHOW ERRORS is identical but only returns rows whose Level is Error.

LIMIT N restricts the result to the first N rows.

An empty result set (zero rows) is valid and means there are no outstanding warnings or errors in the current session.