DDL — Data Definition Language

Goal

Reference for supported DDL operations: creating, altering, and dropping tables, indexes, and constraints.

CREATE TABLE

Basic form

CREATE TABLE t (
 id INTEGER PRIMARY KEY,
 name VARCHAR(100) NOT NULL,
 v BIGINT
);

With table options

CREATE TABLE events (
 id INTEGER PRIMARY KEY,
 ts TIMESTAMP NOT NULL,
 data TEXT
) WITH (append_only = true);

CREATE TABLE ledger (
 id INTEGER PRIMARY KEY,
 amount BIGINT,
 ref_id INTEGER
) WITH (mutation_policy = 'no_delete');

Constraints

• PRIMARY KEY — required for every table (exactly one).
• NOT NULL — column does not accept NULL.
• FOREIGN KEY ... NOT ENFORCED — declarative FK without runtime checking.

CREATE TABLE orders (
 id INTEGER PRIMARY KEY,
 parent_id INTEGER NOT NULL,
 FOREIGN KEY (parent_id) REFERENCES parents (id) NOT ENFORCED
);

Enforced foreign keys are not supported — attempting to create an FK without NOT ENFORCED will return 0A000.

ALTER TABLE

Table-level options

ALTER TABLE t SET (append_only = true);
ALTER TABLE t SET (append_only = false);
ALTER TABLE t SET (mutation_policy = 'no_delete');
ALTER TABLE t SET (mutation_policy = 'unrestricted');

ALTER TABLE t SET (stats_level_max = 2);
ALTER TABLE t SET (stats_reservoir_size = 1000);

Column-level options

ALTER TABLE t ALTER COLUMN v SET (stats_level_max = 1);

Table options reference

append_only = true is equivalent to mutation_policy = 'append_only'.

DROP TABLE

DROP TABLE t;

With DROP TABLE, owned sequences (SERIAL/IDENTITY) are cascadedly dropped, even without CASCADE (PostgreSQL behavior).

CREATE / ALTER / DROP SEQUENCE

AngaraBase supports first-class sequence objects (SEQUENCE) — RM-0.6.3.7, RFC-2026-497. They are persisted in sys_catalog, survive server restart, and back SERIAL/BIGSERIAL and GENERATED [ALWAYS|BY DEFAULT] AS IDENTITY.

CREATE SEQUENCE

CREATE SEQUENCE s1;                                                -- start=1, inc=1, no upper bound
CREATE SEQUENCE s2 START WITH 100 INCREMENT BY 5;
CREATE SEQUENCE s3 MINVALUE 1 MAXVALUE 999 CYCLE;                  -- cyclic counter
CREATE SEQUENCE IF NOT EXISTS s1;                                  -- idempotent

Options (any order): START WITH n, INCREMENT BY n, MINVALUE n / NO MINVALUE, MAXVALUE n / NO MAXVALUE, CYCLE / NO CYCLE.

ALTER SEQUENCE

ALTER SEQUENCE s1 INCREMENT BY 10;
ALTER SEQUENCE s1 RESTART WITH 1;                                  -- reset counter
ALTER SEQUENCE s1 MAXVALUE 1000 NO CYCLE;
ALTER SEQUENCE t_id_seq OWNED BY t.id;                             -- bind to column
ALTER SEQUENCE s1 OWNED BY NONE;                                   -- unbind

DROP SEQUENCE

DROP SEQUENCE s1;
DROP SEQUENCE IF EXISTS s1;
DROP SEQUENCE s1 CASCADE;

Function behavior

See dml.md — “Sequence functions” section (nextval, currval, setval).

SQLSTATE

CREATE INDEX

AngaraBase supports single-column indexes of two types: btree (default) and brin.

-- btree (default)
CREATE INDEX idx_t_v ON t (v);

-- btree (explicit)
CREATE INDEX idx_t_v ON t USING btree (v);

-- brin
CREATE INDEX idx_events_ts ON events USING brin (ts);

BRIN supported key types

BRIN remains an accelerator path with heap fetch + MVCC predicate recheck.

Current bounds

• Single-column indexes only.
• Composite and expression indexes are not supported — 0A000.
• Unsupported index methods (GIN, GiST, etc.) — 0A000.

Expected SQLSTATE

Links

• Data types: data-types.md
• DML (INSERT/UPDATE/DELETE): dml.md
• Partitioning (CREATE TABLE … PARTITION BY): partitioning.md
• Known issues: Known issues