rdbms-playground/docs/adr/0035-advanced-mode-sql-ddl.md

ADR-0035: Advanced-mode SQL DDL

Status

Accepted. Design agreed with the user (2026-05-24); the approach is validated end-to-end by sub-phases 4a / 4a.2 / 4a.3 / 4b / 4c / 4d / 4e / 4f / 4g / 4h / 4i (CREATE TABLE with column- and table-level constraints and foreign keys, DROP TABLE [IF EXISTS], CREATE [UNIQUE] INDEX / DROP INDEX [IF EXISTS], ALTER TABLE add/drop/rename column, ALTER TABLE … ALTER COLUMN TYPE, ALTER TABLE add/drop constraint + add foreign key, ALTER TABLE … RENAME TO, and the 4i verification sweep — completion merge, simple/advanced completion colour, describe of table-level constraints, self-ref FK indicator, and the CREATE-TABLE help/usage refresh — implemented 2026-05-25/26 — plans docs/plans/20260524-adr-0035-sql-ddl-4a.md, …-4a2.md, …-4a3.md, docs/plans/20260525-adr-0035-sql-ddl-4b.md, …-4c.md, …-4d.md, …-4e.md, …-4f.md, …-4g.md, docs/plans/20260526-adr-0035-sql-ddl-4h.md, docs/plans/20260526-adr-0035-sql-ddl-4i.md). Phase 4 is complete (4a–4i all shipped). Amendment 1 (2026-05-26) adds a way to drop a composite UNIQUE via a derived, engine-neutral name (unique_<cols>) that reuses the existing DROP CONSTRAINT <name> grammar — no new syntax, no metadata, the §4g anonymity decision intact (see the amendment below). This is Phase 4 of the ADR-0030 roadmap (the advanced-mode SQL surface), the peer of ADR-0031 (expression grammar), ADR-0032 (SELECT), and ADR-0033 (DML). It clarifies ADR-0030 §4 on how DDL is represented and executed.

Refinements (2026-05-24, pre-implementation /runda round, user-confirmed). Two open micro-calls were settled before 4a: (1) IF [NOT] EXISTS is admitted as a no-op-that-succeeds-with-a-note rather than refused — it is a near-universal cross-vendor idiom (PostgreSQL, MySQL/MariaDB, SQLite, Oracle 23ai), not an engine-specific spelling, so it belongs in the standard surface (§3/§4/§12/§13); (2) INTEGER PRIMARY KEY maps to a plain int primary key, not auto-increment — serial remains the sole auto-increment type (§3).

Context

ADR-0030 fixed the architecture of advanced mode — SQL authored as grammar in the unified tree (not a separate batch parser), with the playground's own type vocabulary and metadata model — and noted that each large grammar piece gets its own focused ADR. Phases 1–3 shipped: the SQL expression grammar (ADR-0031), full SELECT (ADR-0032), and DML — INSERT/UPDATE/DELETE (ADR-0033). Phase 4 is DDL: CREATE / DROP / ALTER TABLE and CREATE / DROP INDEX.

Two things from the earlier phases shape this one:

1. The advanced surface gets its own commands. ADR-0033 established that a SQL statement produces a distinct command (SqlInsert / SqlUpdate / SqlDelete), separate from the simple-mode typed command for the same verb. Those DML commands execute as validated SQL run verbatim — possible only because DML changes no schema and touches no metadata.
2. DDL cannot run verbatim. If CREATE TABLE Orders (id INTEGER) executed as-is, the engine would make the table, but the playground would lose what the user meant: that id is serial, that a REFERENCES clause is a named relationship, that STRICT applies, that the ten-type vocabulary governs. Recovering that needs the parsed statement either way.

ADR-0030 §4 said "DDL → a Command … run the typed executor." That remains right in spirit — DDL is structurally executed, not raw — but it predates the DML build and read as "reuse the simple-mode CreateTable variant." This ADR clarifies it: DDL gets its own advanced commands too, executed structurally (not verbatim). The "verbatim" execution of the DML commands is an implementation convenience available only because nothing about DML required otherwise — not an architectural rule.

Requirements touched: realizes Q4 for DDL; closes the advanced-mode side of table/column/index/constraint/relationship operations; lands the table-rename half of C1 (advanced mode only).

Decision

1. Own per-statement SQL DDL commands (clarifies ADR-0030 §4)

New Command variants, one per statement kind — granularity mirrors the DML phase:

• SqlCreateTable
• SqlAlterTable
• SqlDropTable
• SqlCreateIndex
• SqlDropIndex

They are produced by the unified grammar's ast_builders in advanced mode. Unlike the DML Sql* commands they execute structurally: the handler reads the parsed structure and performs the schema change through the playground's metadata-maintaining machinery — writing __rdbms_playground_columns / __rdbms_playground_relationships, applying STRICT, using the ten-type vocabulary — so an advanced-mode-created object is a first-class playground object, identical to a simple-mode-created one (ADR-0030 §5).

Simple mode is untouched. The existing typed commands (CreateTable, AddColumn, AddRelationship, …) and their grammar are unchanged; advanced SQL DDL is purely additive.

Execution sharing (per the user's steer). The SQL DDL handlers reuse the low-level schema/metadata helpers — the table builder, the metadata writers, the rebuild-table primitive (ADR-0013) — where the underlying operation is genuinely the same, so the two surfaces cannot drift. Where the SQL path is genuinely different (e.g. a CREATE TABLE that declares several inline foreign keys, which has no simple-mode shape), it is implemented directly for clarity rather than bending the simple-mode command shapes to absorb it. Shared where it works; separate where it doesn't.

2. Dispatch — shared entry words, advanced-only alter

create and drop are already simple-mode entry words. They reuse the category-grouped, mode-aware dispatch from ADR-0033 Amendment 1: each appears in both the Simple and Advanced groups of the REGISTRY; in advanced mode the SQL node is tried first and falls back to the simple node when the SQL shape doesn't match. So in advanced mode CREATE TABLE T (id serial) parses as SQL while create table T with pk id(serial) still parses as the simple form — exactly as insert behaves today. alter is a new advanced-only entry word (CommandCategory::Advanced); simple mode keeps its add column / drop column / rename column / change column verbs and gains no alter.

3. Type vocabulary (restates ADR-0030 §5)

The type-name slot accepts the playground keywords directly (text, int, real, decimal, bool, date, datetime, blob, serial, shortid) and standard-SQL aliases mapped onto them: integer/smallint/bigint → int; varchar/char → text; boolean → bool; timestamp → datetime; numeric → decimal; float/double precision → real; binary/varbinary → blob. A length/precision argument (varchar(255), numeric(10,2)) is accepted and ignored — the playground's types are unparameterised. Engine storage-type names are neither accepted as input nor shown (§9).

The map is purely lexical: INTEGER PRIMARY KEY becomes a plain int primary key — it is not treated as auto-increment, unlike the engine's rowid-alias idiom. Auto-increment is reached only through the explicit serial type (id serial primary key). This keeps the engine's storage behaviour from leaking into the standard surface and matches ADR-0005's single-auto-increment-type model.

4. The DDL surface (full; Q4, no pre-emptive cuts)

CREATE TABLE <name> ( <element>, … )

• Column elements: <name> <type> [constraints…], where the column constraints are the ADR-0029 set spelled in SQL: NOT NULL, UNIQUE, PRIMARY KEY, DEFAULT <expr>, CHECK (<expr>), and an inline REFERENCES <T>(<col>) [ON DELETE …] [ON UPDATE …] (§5).
• Table elements: PRIMARY KEY (<col>, …) (single and compound), UNIQUE (<col>, …), CHECK (<expr>), [CONSTRAINT <name>] FOREIGN KEY (<col>) REFERENCES <T>(<col>) [ON DELETE …] [ON UPDATE …] (§5).
• CHECK and DEFAULT expressions reuse the ADR-0031 sql_expr grammar (the same fragment WHERE/HAVING/projections use).
• CREATE TABLE IF NOT EXISTS <name> … is admitted: when the table already exists the statement is a no-op that succeeds with a note ("table already exists — skipped") instead of the plain-form "table already exists" error. IF NOT EXISTS is a near-universal cross-vendor idiom, not an engine-specific spelling, so it is part of the standard surface (refines §12).

DROP TABLE [IF EXISTS] <name> → SqlDropTable. Cascade of inbound relationships follows the existing drop table semantics. IF EXISTS is admitted (universal across the major engines): dropping an absent table is then a no-op that succeeds with a note instead of the plain-form "no such table" error.

ALTER TABLE <name> <action> → SqlAlterTable, where <action> covers, mapping to the existing low-level operations:

SQL action	Underlying operation
ADD COLUMN <name> <type> [constraints]	add-column (ADR-0013 rebuild where needed)
DROP COLUMN <name>	drop-column
RENAME COLUMN <old> TO <new>	rename-column
ALTER COLUMN <name> TYPE <type>	change-column-type (§5 conversion)
ADD [CONSTRAINT <name>] <table-constraint>	add-constraint / add-relationship (FK)
DROP CONSTRAINT <name>	drop-constraint
RENAME TO <new>	table rename (§6, new low-level op)

CREATE [UNIQUE] INDEX [<name>] ON <table> (<col>, …) → SqlCreateIndex, mapped to the ADR-0025 index machinery; UNIQUE sets the index's uniqueness (a small extension to ADR-0025's index model if it does not already carry the flag, called out in §13).

DROP INDEX <name> → SqlDropIndex.

5. Foreign keys → named relationships

A REFERENCES / FOREIGN KEY clause is the SQL spelling of an ADR-0013 relationship. Because SqlCreateTable is its own command carrying the whole parsed structure, a CREATE TABLE that declares FK columns creates the table and its relationship metadata together — one statement, one command, one transaction, one undo step (§10). No decomposition into separate commands is needed.

• ON DELETE / ON UPDATE → the ADR-0013 referential actions.
• A CONSTRAINT <name> FOREIGN KEY … names the relationship; an unnamed FK is auto-named by the existing ADR-0013 convention.
• ALTER TABLE child ADD [CONSTRAINT <name>] FOREIGN KEY (<col>) REFERENCES <P>(<col>) … adds a relationship to an existing table (the clean 1:1 with add-relationship).
• FK column type compatibility follows Type::fk_target_type (ADR-0011) unchanged.

6. Table rename — advanced mode only (C1)

ALTER TABLE <old> RENAME TO <new> is advanced-mode only; there is no simple-mode rename-table verb. It needs a genuinely new low-level operation (none exists today): within one transaction, rename the table in the database, rename its data/<table>.csv file, and update every metadata row that names it — the column-metadata rows, both ends of any relationship in __rdbms_playground_relationships that references the old name, and the table-level CHECK rows in __rdbms_playground_table_checks (added in 4a.3; keyed by table_name). Name validation and __rdbms_* rejection apply to the target. This closes the rename half of C1 for the advanced surface.

7. Column type conversion — one engine, mode-appropriate policy

The per-cell classification of ADR-0017 (clean / lossy / incompatible, plus static refusals for playground-type-specific targets such as → serial and ↔ blob) is a property of the type set, shared by both modes. The policy on the lossy tier differs by mode:

Tier	Simple mode	Advanced mode (ALTER COLUMN … TYPE)
clean	auto-convert	auto-convert
incompatible	refuse (friendly)	refuse (friendly) — real SQL errors too
static-refused (→serial, ↔blob, …)	refuse	refuse — our own types have no SQL meaning to mirror
lossy (3.14→3)	refuse by default; --force-conversion opts in	perform it (what SQL does), with a post-op "N values converted with loss" note; no force flag

Rationale: simple mode protects up front; advanced mode trusts the user like SQL does and lets undo catch regrets. A lossy advanced conversion is snapshotted (§10), so it is one undo away — there is no silent irreversible loss, and no need to drop to simple mode to "force". Conversions that exist only in the playground's vocabulary stay protected in both modes. The simple-mode --force-conversion / --dont-convert flags are unchanged and have no SQL spelling (advanced mode always performs the conversion); the Postgres USING <expr> clause is not adopted (§12).

8. Constraints

Column- and table-level constraints map to the ADR-0029 model: NOT NULL, UNIQUE, PRIMARY KEY (incl. compound, table-level), DEFAULT <expr>, CHECK (<expr>). A populated-column constraint addition reuses ADR-0029's pre-flight dry-run guard. CHECK / DEFAULT expressions are stored as the SQL the user could re-enter in advanced mode (ADR-0030 §11) — one syntax, not a third.

9. Engine neutrality (ADR-0030 §7)

No engine type names in or out (§3). STRICT is applied internally by the create path; it is not in the authored grammar, so typing it is an ordinary parse error, not a surfaced engine feature. Parse errors, out-of-subset refusals, and execution failures route through the friendly-error layer (ADR-0019) with engine-neutral wording.

10. Persistence, metadata, history, replay, undo

• Structural execution keeps project.yaml, the metadata tables, and the CSV layer correct with the same guarantees as the simple-mode path (ADR-0015 §6 ordering preserved).
• history.log records the literal submitted SQL line; replay re-runs it through the one walker with the advanced view active. create / drop / alter are schema-write entry words, not in ADR-0034 Amendment 1's app-lifecycle skip set, so SQL DDL replays as a write (re-applied) with no replay-filter change — unlike undo / redo, which had to be added to that skip set.
• Undo (ADR-0006): each SQL DDL statement is a user mutation carrying a source, so it is snapshotted by the worker hook and is one undo step — including a CREATE TABLE with foreign keys, precisely because it is a single command (§5) rather than a decomposed sequence.

11. Ambient assistance comes for free (ADR-0030 §8)

Because the DDL is grammar in the unified tree, the walker mechanisms apply with no DDL-specific assistance code: syntax highlighting, the [ERR]/[WRN] validity indicator (ADR-0027), the per-command parse-error usage skeleton (ADR-0021), and the completion engine.

What each grammar node still authors (this is writing the grammar, not bolting assistance on afterwards): the correct IdentSource on every schema-name slot — so ALTER TABLE/DROP TABLE/DROP INDEX and REFERENCES T(col) / CREATE INDEX ON T (cols) complete from the SchemaCache; the per-node hint + usage catalog keys (as the app-command nodes carry help_id / usage_ids); and the DDL-specific walker diagnostics with their catalog keys — the DDL peers of the DML diagnostics ADR-0033 added (e.g. unknown type, column-already-exists, FK column-type mismatch, the §7 lossy-conversion note). The integration is structural, not free of authoring.

12. Out of scope

• Per ADR-0030 §3: views, triggers, transaction control, PRAGMA, ATTACH/DETACH, VACUUM, virtual tables, multi-statement batches. One statement per submission; a trailing ; is tolerated.
• The Postgres USING <expr> conversion clause (§7) — heavy (per-row expression evaluation), dialect-specific, and unable to express playground-type targets.
• The simple-mode --dont-convert semantics have no SQL form (advanced ALTER COLUMN TYPE always converts).
• The DSL → SQL teaching echo (ADR-0030 §10) is Phase 5, a separate ADR — not this one.
• Engine-specific DDL spellings (AUTOINCREMENT, WITHOUT ROWID, collations) — the grammar admits the standard surface; extras are ordinary parse errors. (IF [NOT] EXISTS was reclassified into scope — see §4 — as a near-universal cross-vendor idiom rather than an engine-specific spelling.)

13. Phased implementation plan

Sub-phases, each opening with the smallest end-to-end slice and each with an explicit exit gate + a written Devil's-Advocate gate, mirroring ADR-0033's structure:

• 4a — Dispatch + CREATE TABLE core. Advanced create dispatch; SqlCreateTable for columns + types (the §3 map, incl. the two-word double precision and discarded length args) + the clean-reuse column constraints only — NOT NULL / UNIQUE / column-level PRIMARY KEY — + single/compound table-level PRIMARY KEY, plus IF NOT EXISTS (no-op-with-note, §4). Reuses do_create_table, whose inline-PK rule is aligned with the rebuild generator schema_to_ddl (inline only a first-column single PK) so a created table and its rebuilt form have identical DDL; serial autoincrement is independent of inline-vs-table-level PK (the insert path computes the next value), verified by round-trip tests. No FK (4b); no DEFAULT/CHECK/table-level UNIQUE (4a.2).
• 4a.2 — Per-column CHECK/DEFAULT + composite UNIQUE(a,b). Split out (2026-05-24) and re-scoped (2026-05-25, user-confirmed) to the constraints that need no new internal table: (1) CHECK/DEFAULT via the full sql_expr surface stored as raw SQL text — sql_expr is validate-only (no Expr AST for compile_check_sql/ColumnSpec), so a separate execution path captures the raw expression text; per-column CHECK reuses the existing __rdbms_playground_columns.check_expr column, DEFAULT round-trips via the engine's native PRAGMA table_info; (2) composite UNIQUE(a,b) — a new TableSchema.unique_constraints field, detected on read via the UNIQUE-constraint index (PRAGMA index_list origin u), round-tripped through YAML, with save/load/rebuild tests.
• 4a.3 — Table-level / multi-column CHECK(…). (Implemented 2026-05-25 — plan docs/plans/20260525-adr-0035-sql-ddl-4a3.md.) Split from 4a.2 (2026-05-25, user-confirmed) because SQLite exposes no PRAGMA for CHECK constraints, so a table-level CHECK cannot be read back from the engine and needs a new __rdbms_* metadata table as its source of truth (the ADR-0012/0013 pattern) — a distinct architectural step. Landed as __rdbms_playground_table_checks (table_name, seq, check_expr); the builder distinguishes a table-level CHECK from a column-level one by element position (no column-def open). Composite UNIQUE deliberately stays PRAGMA-detected (engine-reportable, unlike CHECK). (The general rule: a DDL feature needs new model/execution work only when it introduces a structure simple mode could never produce, or an expression the structural helper cannot consume — cf. the UNIQUE-index flag in 4d and the rename op in 4h.)
• 4b — Foreign keys in CREATE TABLE. (Implemented 2026-05-25 — plan docs/plans/20260525-adr-0035-sql-ddl-4b.md.) Inline REFERENCES <parent>[(<col>)] [ON DELETE/UPDATE …] + table-level [CONSTRAINT <name>] FOREIGN KEY (<col>) REFERENCES … → ADR-0013 relationship metadata, written in the create transaction (one undo step). Reuses the relationship name/uniqueness/metadata helpers shared with add relationship; do_create_table emits the FOREIGN KEY clause identically to schema_to_ddl. Self-references (parent = the table being created, validated against the in-statement columns/PK) and the bare REFERENCES <parent> form (resolves to the parent's single-column PK; composite → error) are both supported (user-confirmed). Inline FKs are auto-named; only the table-level form takes CONSTRAINT <name>. PK-target only (UNIQUE-target deferred with add relationship); Type::fk_target_type (ADR-0011) governs type compatibility.
• 4c — DROP TABLE [IF EXISTS] → SqlDropTable. (Implemented 2026-05-25 — plan docs/plans/20260525-adr-0035-sql-ddl-4c.md.) Reuses do_drop_table (cascade parity + the inbound-relationship refusal + metadata cleanup), so it matches the simple drop table; IF EXISTS on an absent table is a no-op-with-note (a new DropOutcome::Skipped mirroring CreateOutcome::Skipped; journalled, no snapshot, §4). drop is a shared entry word: drop table parses as SqlDropTable in advanced mode, drop column/relationship/ index/constraint fall back to the simple drop node. Advanced- mode drop completion now surfaces the SQL table (the shared-entry-word behaviour from create, ADR-0033 Amendment 3); the DSL drops still parse via fallback — 4i grows the surface as DROP INDEX lands in 4d.
• 4d — CREATE [UNIQUE] INDEX / DROP INDEX → SqlCreateIndex / SqlDropIndex. (Implemented 2026-05-25 — plan docs/plans/20260525-adr-0035-sql-ddl-4d.md.) Both reuse the ADR-0025 executors (do_add_index / do_drop_index), like 4c reused do_drop_table. CREATE [UNIQUE] INDEX [IF NOT EXISTS] [<name>] ON <T> (cols) (the unnamed form auto-named per ADR-0025; the leading [UNIQUE] is a concrete-keyword Choice, the optional name an on-led-first selector mirroring the drop index positional selector) and DROP INDEX [IF EXISTS] <name> (name-only — the positional drop index on T(…) stays the simple form via fallback). IF [NOT] EXISTS reuses the 4c no-op-with-note skip (journalled, not snapshotted). CREATE UNIQUE INDEX is admitted (user-confirmed 2026-05-25): ADR-0025 deferred UNIQUE indexes for the simple-mode DSL, but advanced mode "trusts the user like SQL does" (§7) — so the model gains an IndexSchema.unique flag (additive YAML, version 1; rebuild re-emits CREATE UNIQUE INDEX; the structure view + items panel mark [unique]), recorded as ADR-0025 Amendment 1. Simple-mode add unique index stays deferred. create/drop each gain a second advanced node, exercising the all-candidates dispatch (decide tries every advanced candidate).
• 4e — ALTER TABLE add/drop/rename column. (Implemented 2026-05-25 — plan docs/plans/20260525-adr-0035-sql-ddl-4e.md.) alter is a new advanced-only entry word (like select/with); ALTER TABLE <T> ADD COLUMN <col> <type> [NOT NULL|UNIQUE|DEFAULT| CHECK] | DROP COLUMN <col> | RENAME COLUMN <old> TO <new> → SqlAlterTable { AlterTableAction }, runtime-decomposed to the existing do_add_column / do_drop_column / do_rename_column (one undo step each) — no new worker layer. The COLUMN keyword is required (reserves bare RENAME TO for 4h, ADD CONSTRAINT for 4g); ADD COLUMN takes column constraints only (no PK / inline REFERENCES). do_add_column was extended to consume the SQL raw-text default_sql / check_sql (DEFAULT/CHECK; sql_expr is validate-only — the 4a.2 mechanism), so ADD COLUMN reaches parity with CREATE TABLE's column constraints. Drop/rename column now refuse a column a CHECK references — the 4a.3 deferral, extended (user-confirmed) to both table-level and column-level CHECKs — detected up-front by tokenizing the raw CHECK text (check_references_column, skipping string literals); for RENAME the column's own column-level CHECK counts (it drifts too), for DROP it does not (it drops with the column). This lives in the shared executors, so it guards both the simple drop/rename column and the SQL surface, fixing a latent rename-drift bug (a native rename rewrites the live CHECK but leaves the stored text — table-level in __rdbms_playground_table_checks or column-level in __rdbms_playground_columns — stale, breaking a later rebuild). SQL DROP COLUMN over an index-covered column is refused (no --cascade SQL spelling — matches SQLite + the simple default; user-confirmed). The shared column executors (and do_add_index) also gained an internal-__rdbms_*-table guard (refuse as "no such table"), closing a pre-existing exposure on both surfaces (user-confirmed). The friendly wording of the CHECK-guard refusal is H1. (The broader internal-table guard on do_change_column_type / do_add_constraint / do_add_relationship is a tracked follow-up.)
• 4f — ALTER TABLE … ALTER COLUMN TYPE (the §7 conversion model + the lossy-with-note path). (Implemented 2026-05-25 — plan docs/plans/20260525-adr-0035-sql-ddl-4f.md.) A fourth AlterTableAction::AlterColumnType, runtime-decomposed to the existing change_column_type executor with ChangeColumnMode::ForceConversion — which is the §7 advanced policy: lossy cells are performed and counted (the engine-neutral client_side.transformed_lossy note fires), incompatible cells refuse, and the ADR-0017 static refusals (↔ blob, same-type, date ↔ datetime, non-int → serial) refuse in both modes. int → serial is allowed (auto-fills nulls, adds UNIQUE if non-PK — ADR-0018 §8; the §7 "static-refused →serial" summary is looser than the code). No force flag, no USING, no SET DATA TYPE synonym (§7/§12); undo is the advanced safety net. The grammar adds a fourth action branch leading on alter, discriminated in the builder by the type keyword (unique — ADD COLUMN's type is an ident); the type slot reuses SQL_TYPE. The internal-__rdbms_* guard was folded into do_change_column_type (user-confirmed 2026-05-25), closing the simple change column exposure too. (The remaining internal-table guard on do_add_constraint / do_add_relationship rides in 4g.)
• 4g — ALTER TABLE add/drop constraint, add foreign key. (Implemented 2026-05-25 — plan docs/plans/20260525-adr-0035-sql-ddl-4g.md.) ALTER TABLE <T> ADD [CONSTRAINT <name>] (CHECK (…) | UNIQUE (…) | FOREIGN KEY (…) REFERENCES …) and DROP CONSTRAINT <name>. ADD scope (user- confirmed): CHECK + composite UNIQUE + FK; ADD PRIMARY KEY is refused (every table already has a PK) and a named UNIQUE is refused (composite UNIQUE is anonymous in our model — PRAGMA-detected, §4a.2). Each ADD reuses an existing low-level path: table-CHECK and composite-UNIQUE rebuild the table (dry-run guards reject existing rows that would violate), FK decomposes to add_relationship (the same machinery add 1:n relationship uses — bare REFERENCES <P> resolves to the parent's single PK; create_fk = false as the column must exist). DROP CONSTRAINT (user-confirmed) resolves the name to a named table-CHECK then a named FK whose child is <T>, else refuses. Named table-CHECK round-trip (user-confirmed): the CHECK_TABLE metadata gains a nullable name column (rebuild-only arrival — a pre-4g project gains it on rebuild; a named CHECK add on an un-upgraded project is refused with a friendly "rebuild first" message), and project.yaml's check_constraints is extended to carry the name ({expr, name} mapping; the bare-string form still reads, name = None) so a named CHECK survives a rebuild — rebuild reconstructs from the yaml. The internal-__rdbms_* guard was folded into do_add_constraint / do_add_relationship, completing the 4d/4e/4f guard class. One undo step per statement.
• 4h — ALTER TABLE … RENAME TO (the §6 new low-level op). (Implemented 2026-05-26 — plan docs/plans/20260526-adr-0035-sql-ddl-4h.md.) The one genuinely new low-level executor in Phase 4 (do_rename_table): a native engine RENAME TO (structure-preserving — no rebuild) plus reconciliation, in one transaction (commit-db-last), of everything the engine does not track — every metadata row that names the table (__rdbms_playground_columns, both ends of __rdbms_playground_relationships, __rdbms_playground_table_checks), the CSV file (via the existing persistence rewrite+delete path: rewritten_tables = [new], deleted_tables = [old] — no new persistence method), and CHECK text that qualifies a column with the old table name (T.age → U.age, both column- and table-level — a planning-/runda finding: the engine rewrites the live CHECK but the stored text would drift and break a fresh rebuild; rewrite_check_table_qualifier keeps them in step; bounded because a CHECK references only its own table). Grammar: a fifth action, AlterTableAction::RenameTable { new }, added by splitting the rename verb into one branch with an inner Choice on a distinct second keyword (column → rename-column, to → rename-table — the §6.1 trap-safe pattern); the new-name slot mirrors the CREATE TABLE name slot (IdentSource::NewName + the reject_internal_table parse validator). Refusals (user-confirmed 2026-05-26): rename to the same name, to an existing other table, to an __rdbms_* name, or of a non-existent table. Collision checks are case-insensitive (the engine matches names that way), with an engine-neutral pre-check so a case-only rename or a case-insensitive clash never surfaces the raw engine error (a finished-slice /runda finding). Auto-named indexes and relationships keep their stale names (only the table-name columns update; ADR-0035 §6 scope — user-confirmed; documented collision caveat). One undo step (the whole-project snapshot). Advanced-mode only; closes the rename half of C1.
• 4i — Verification sweep (completes Phase 4). (Implemented 2026-05-26 — plan docs/plans/20260526-adr-0035-sql-ddl-4i.md.)
  • (a) CREATE TABLE help/usage skeleton refreshed for the 4a.2 DEFAULT/CHECK/composite-UNIQUE, 4a.3 table-CHECK, and 4b FK forms (the index forms already carried their own since 4d).
  • (b) describe of table-level constraints — TableDescription gained unique_constraints + check_constraints, rendered in a "Table constraints:" section (composite UNIQUE, table CHECK incl. named CHECKs). The per-column [unique]-index marker shipped in 4d.
  • (c) self-ref FK indicator — schema_existence_diagnostics collects the CREATE TABLE target(s) (IdentSource::NewName, role table_name) and exempts a Tables reference matching one from the unknown-table flag, so a self-referencing FK no longer pre-flags the not-yet-created table; a FK to a genuinely-unknown other table still flags.
  • (d) shared-entry-word completion merge — at the advanced-mode entry-word boundary, completion_probe_in_mode walks every candidate node and unions the viable (Incomplete) ones' continuations, so drop offers table·index·column·relationship·constraint and drop rel → relationship (was an empty dead-end). Completion-only (the parse path is untouched); deeper positions keep the committed walk.
  • (e) simple-vs-advanced completion colour — each continuation is classified Both/Advanced/Simple and, only when the candidate list mixes modes, coloured (mode_advanced/mode_simple, Both = token-kind) and block-ordered Both → Advanced → Simple (user-chosen design, 2026-05-26). Single-mode lists keep the token-kind colours.
  • Staples: matrix/typing-surface coverage extended (completion + describe tests); engine-neutral wording held (the vocab audit covers the new strings); undo-parity is N/A for 4i — every change is read-side (completion / diagnostics / describe / help), so no undo steps are introduced.

Amendment 1 — Dropping a composite UNIQUE constraint (2026-05-26)

A whole-Phase-4 /runda surfaced that a composite UNIQUE(a,b) — kept anonymous by design (§4a.2, and §4g refused a named UNIQUE add) — has no way to be dropped: DROP CONSTRAINT <name> (§4g) resolves only a named table-CHECK or a named FK, so recreating the table was the only escape. This amendment adds a drop path. Written with explicit user approval; the plan is docs/plans/20260526-adr-0035-composite-unique-drop-f1f2f3.md. Implemented 2026-05-26.

It does not reverse the §4g anonymity decision. Storage stays a bare column-list (unique_constraints: Vec<Vec<String>>, PRAGMA-detected) and a UNIQUE still cannot be named on ADD. The addition is purely a derived, engine-neutral name used to display and address the constraint on drop.

The derived name (user-decided: derived, no storage; unique_<cols>)

The name is a deterministic function of the column list — unique_<col1>_<col2>… — recomputed live wherever it is shown or matched. Nothing is persisted: the constraint remains a bare column-list, so the name round-trips for free and needs no metadata table and no rebuild-arrival migration (the cost §4a.3 deliberately avoided). If a column in the UNIQUE is later renamed, the displayed name tracks it — arguably more correct than a frozen stored name. Alternatives weighed and rejected: naming UNIQUEs with a user-supplied name + new metadata table (reverses §4g; heaviest), and a positional drop … unique (cols) form (needs new grammar). The derived name reuses the existing DROP CONSTRAINT <name> grammar — no new syntax.

Surfaces

• describe / structure view. The "Table constraints:" section (4i b) annotates each composite UNIQUE with its name: unique_b_c: UNIQUE (b, c).
• ALTER TABLE <T> DROP CONSTRAINT <name> (advanced-SQL only, matching the §4g ADD form). do_drop_constraint_by_name gains a third resolution step after named table-CHECK and named FK: it recomputes the derived name of each composite UNIQUE on <T> and matches. On a single match it rebuilds the table without that entry (the do_alter_add_unique rebuild in reverse). A name matching more than one UNIQUE is refused as ambiguous (e.g. a column literally named b_c colliding with UNIQUE (b, c)) — it never guesses which to drop. Resolution order means a user-named CHECK/FK with the same string shadows a derived UNIQUE name; the distinctive unique_ prefix makes this unlikely and it is documented, not guarded.

Dropping a column a composite UNIQUE covers (F1)

do_drop_column gains an up-front guard (alongside the index-covering and CHECK guards): a column participating in any composite UNIQUE is refused with the constraint's derived name and the actionable drop command — cannot drop \T.c` … part of the UNIQUE constraint `unique_b_c` (b, c); drop that constraint first (`alter table T drop constraint unique_b_c`). The refusal itself is unchanged (the engine already refuses it); the message becomes engine-neutral and actionable. Single-column UNIQUE column drops are a **parallel** gap (different mechanism — ADR-0029 column-level drop constraint`) and are out of scope here.

Amendment 2 — Standard-first dialect + ALTER COLUMN constraint gap-fill (2026-05-27)

Designing the DSL → SQL teaching echo (ADR-0030 §10, specified in ADR-0038) surfaced two related things about this ADR's surface. First, a dialect drift: ADR-0030 frames advanced mode as "the standard-SQL surface," but §4f shipped the type-change verb as bare ALTER COLUMN … TYPE — the PostgreSQL shorthand — and explicitly declined the ISO SET DATA TYPE synonym (§4f, line "no SET DATA TYPE synonym"). Second, gaps: advanced mode has no way to toggle NOT NULL or DEFAULT on an existing column, though simple mode does (ADR-0029 add/drop constraint), and the rebuild primitive that would back them is already in place (it backs §4f type-change and §4g constraint-add). This amendment records a dialect stance and fills the clean gaps so the echo can emit portable SQL that is also runnable in advanced mode. Recorded with explicit user approval (2026-05-27).

The dialect stance — standard-first (refines ADR-0030)

Where ISO SQL provides a spelling, the authored grammar's canonical form is the ISO one, and the echo emits the ISO form. A widely- recognised vendor shorthand may be accepted as a synonym (so a learner who knows it is not punished), but it is never the canonical or emitted form. Where ISO provides no spelling for an operation the playground teaches, one widely-recognised vendor spelling is adopted as a deliberate, documented extension — not silently. This realigns the surface with ADR-0030's stated posture and makes the divergence a conscious, recorded choice rather than drift.

The stance applies to the whole advanced surface going forward; this amendment exercises it on the ALTER COLUMN family.

Type change: ISO SET DATA TYPE canonical, TYPE retained as a synonym

Reverses §4f's "no SET DATA TYPE synonym." The grammar now accepts both ALTER COLUMN <c> SET DATA TYPE <type> (ISO; canonical) and ALTER COLUMN <c> TYPE <type> (PostgreSQL; accepted synonym, no breakage for already-shipped usage). Both decompose to the same §4f AlterColumnType action and the same ChangeColumnMode::ForceConversion executor — semantics are unchanged; only the accepted spelling set and the canonical/echoed form change. The echo (ADR-0038) emits SET DATA TYPE.

New: SET/DROP DEFAULT (ISO) and SET/DROP NOT NULL (the one extension)

Four new AlterColumnType-family actions under ALTER COLUMN <c>:

Spelling	Standing	Decomposes to
SET DEFAULT <expr>	ISO-standard	do_set_column_default (raw-SQL — see note)
DROP DEFAULT	ISO-standard	do_drop_constraint(Default)
SET NOT NULL	documented extension	do_add_constraint(NotNull)
DROP NOT NULL	documented extension	do_drop_constraint(NotNull)

Implementation correction (2026-05-27, during build). The draft said SET DEFAULT decomposes to do_add_constraint(Default). It cannot: do_add_constraint's Constraint::Default(Value) carries a typed Value (the simple-mode shape), but advanced SET DEFAULT <expr> is raw sql_expr text with no AST (it may be (1 + 1), a function call, …). So SET DEFAULT decomposes to a small dedicated executor do_set_column_default(table, column, default_sql) that sets the column's raw default_sql and rebuilds — mirroring do_add_constraint's Default branch (the §6 serial/shortid refusal, no dry-run — a default never touches existing rows) but accepting raw SQL instead of a Value. SET NOT NULL / DROP NOT NULL / DROP DEFAULT reuse the ADR-0029 do_add_constraint / do_drop_constraint executors unchanged.

SET DEFAULT/DROP DEFAULT are taken directly from the ISO <alter column action> set. NOT NULL toggling has no ISO spelling — in the standard NOT NULL is a column constraint, not an in-place ALTER COLUMN verb, and the vendors diverge (PostgreSQL SET/DROP NOT NULL; SQL Server ALTER COLUMN <c> <type> NOT NULL; MySQL MODIFY; Oracle MODIFY). Per the stance, one spelling is adopted as a deliberate extension: PostgreSQL's SET/DROP NOT NULL, chosen because it is the only form that is type-independent (it does not force the user to restate the column type), reads as plain English, and composes uniformly with the ISO SET/DROP DEFAULT it sits beside.

SET DEFAULT's value slot reuses the §4a.2 / §4e raw sql_expr default mechanism (default_sql), so a default may be any expression the create-table DEFAULT accepts — one syntax, not a third (ADR-0030 §11).

Execution — rebuild-backed, no new low-level op

Each new action runtime-decomposes to an existing ADR-0029 executor (do_add_constraint / do_drop_constraint), exactly as §4e/§4f decompose their actions — the populated-column pre-flight dry-run guard (ADR-0029 §5) and the internal-__rdbms_* guard come for free. No new worker layer. The grammar discriminates the ALTER COLUMN <c> … tail by its leading keyword: type / set data type (type change), set not null / drop not null, set default / drop default — the set/drop lead is new alongside §4f's type lead.

Parity reached, and the one residual gap

This brings advanced mode to constraint-modification parity with simple mode (ADR-0029) for NOT NULL and DEFAULT — add and drop, both directions. It closes the simple↔advanced asymmetry the echo design flagged for those ops.

Residual gap (deliberately not closed here): dropping a column-level UNIQUE or CHECK (the single-column, anonymous constraints simple mode adds via ADR-0029 add constraint unique/check). DROP CONSTRAINT <name> (§4g) + the derived composite-UNIQUE name (Amendment 1) resolve table-level / named constraints; a single- column column-level UNIQUE lives as the column's unique flag and a column-level CHECK is likewise anonymous, so neither has a portable name to address. This is the same class Amendment 1 called a "parallel gap … out of scope." Consequently ADR-0038's catalogue marks drop constraint unique/check from T.col as no headline echo (a residual gap), rather than inventing a name or a recipe. Flagged for the user; closing it (e.g. extending the derived-name approach to single- column UNIQUE) would be its own small follow-up.

Engine neutrality holds (the rebuild stays hidden)

The chosen spellings are portable SQL, not engine features. The fact that this engine satisfies SET NOT NULL / SET DATA TYPE via a table rebuild (because it lacks in-place ALTER) is a Category-1 engine implementation detail (ADR-0038's taxonomy) and stays invisible — no recipe, no rebuild steps surfaced — exactly as §9 and ADR-0030 §7 require. A learner sees the standard statement; the engine's means of honouring it is not the lesson.

Consequences

• Advanced mode reaches DDL parity with simple mode and adds table-rename, so a learner can build and evolve a whole schema in standard SQL with the playground's types, metadata, and safety intact.
• The command set grows by five Sql* DDL variants; the worker gains their handlers, which lean on shared low-level helpers where the operation matches the simple-mode path and stand alone where the SQL surface is genuinely richer (multi-FK CREATE TABLE).
• One genuinely new capability — table rename — adds a low-level op that the simple mode does not have; it must keep the CSV file name and the relationship metadata in step with the table name.
• ADR-0030 §4 is clarified (own Sql* DDL commands, structurally executed); no behaviour of the shipped DML/SELECT phases changes.
• The conversion model unifies simple and advanced without a force flag in SQL, relying on undo (ADR-0006) as the advanced-mode safety net — a concrete payoff of having shipped undo first.

See also

• ADR-0030 — the advanced-mode architecture; this is its Phase 4 and clarifies §4 (DDL representation) and restates §5 (types) / §7 (neutrality) / §8 (assistance) / §11 (persistence).
• ADR-0033 — the DML phase; source of the category-grouped mode-aware dispatch (Amendment 1) reused for shared entry words.
• ADR-0031 — sql_expr, reused for CHECK / DEFAULT.
• ADR-0013 — relationships + the rebuild-table primitive that the ALTER/FK handlers build on.
• ADR-0017 — the column type-change classification §7 shares.
• ADR-0029 — column constraints; ADR-0025 — indexes; ADR-0011 — FK column-type compatibility; ADR-0005 — the ten-type vocabulary.
• ADR-0006 — undo; each DDL statement is one undo step (§10).