Creating, Altering & Destroying Tables & Indexes

The create/alter/destroy and index/destroy ... on statements let you manage ad hoc tables and indexes — staging, archive, or audit tables — directly from ObjectQuel, without an entity class or a migration. They operate outside the entity-mapped schema: for tables you intend to keep and version long-term, prefer @Orm\Column/@Orm\Index annotations and Sculpt migrations instead.

Creating Tables

create takes a table name followed by a parenthesized, comma-separated list. Entries may appear in any order and repeat as needed — the list can hold any mix of the following:

$entityManager->executeQuery("
    create ArchiveLog (
        id = integer identity,
        message = string(500),
        created_at = datetime,
        primary key (id)
    )
");

create and destroy return no rows — executeQuery() returns null for both.

Column Types

Column types are the same abstract vocabulary @Orm\Column annotations use (see Annotations Reference). A length limit or decimal precision/scale follows the type name in parentheses where noted below:

$entityManager->executeQuery("
    create Invoice (
        number = string(20),
        amount = decimal(10,2)
    )
");

Constraints

A column accepts any combination of the following, in any order. Columns are NOT NULL by default, matching @Orm\Column's own nullable parameter — add nullable to a column to allow NULL values:

$entityManager->executeQuery("
    create Contact (
        email = string(255),
        phone = string(20) nullable
    )
");

unsigned precedes the type name — unsigned integer — mirroring C's declaration order. A bare unsigned with no type name after it defaults to unsigned integer. Declaring it on a type with no signed/unsigned distinction (string, for example) is rejected at parse time regardless of target engine:

$entityManager->executeQuery("
    create Inventory (
        stock = unsigned,
        weight = unsigned decimal(8,2)
    )
");

Primary Keys

A primary key isn't a column-level constraint — it's declared with a standalone primary key (col, ...) entry in the column list, in any position, listing one or more columns in physical key-column order:

$entityManager->executeQuery("
    create OrderLine (
        order_id = integer,
        line_no = integer,
        sku = string(20),
        primary key (order_id, line_no)
    )
");

Foreign Keys

The column list may also contain any number of foreign key (col) references Table [(col)] [on delete action] [on update action] entries, in any position — the same underlying grammar as @Orm\ForeignKey/@Orm\ForeignKeyAction (see Foreign Key Constraints), just spelled as DDL instead of an annotation:

$entityManager->executeQuery("
    create OrderTable (
        customer_id = integer,
        foreign key (customer_id) references Customer (id) on delete cascade
    )
");

The referenced column may be omitted — references Customer then resolves to the target table's own primary key automatically. This only works when that primary key is a single column; a target with no primary key, or a composite one, is rejected with an error telling you to name the column explicitly:

$entityManager->executeQuery("
    create Shipment (
        customer_id = integer,
        foreign key (customer_id) references Customer
    )
");

on delete/on update may appear in either order, or be omitted entirely — omitted, they default to RESTRICT/NO ACTION respectively, matching @Orm\ForeignKeyAction's own defaults. Valid actions are restrict, cascade, set null, and no action.

Embedded Indexes

The column list may also contain [unique|fulltext] index name (col, ...) entries — the same grammar as the standalone index statement further down this page, minus the on Table is phrasing, which is redundant once already scoped to the table being created:

$entityManager->executeQuery("
    create Catalog (
        sku = string(20),
        tenant_id = integer,
        description = text,
        unique index catalog_sku_uniq (sku),
        index catalog_tenant_idx (tenant_id),
        fulltext index catalog_description_idx (description)
    )
");

Each entry is assembled into a real, separate index-creation statement behind the scenes — functionally identical to writing the standalone index statement afterward, just declared in one place instead of several.

Temporary Tables

Add temporary right after create for a session-scoped table — visible only to the current connection, and gone once the connection closes:

$entityManager->executeQuery("
    create temporary StagingTotals (
        user_id = integer,
        total = decimal(10,2)
    )
");

Disposal is always explicit — drop a temporary table with destroy when you're done with it, or let it disappear with the connection.

if not exists

Append if not exists to make create a no-op instead of an error when the table is already there:

$entityManager->executeQuery("
    create ArchiveLog (id = integer) if not exists
");

Destroying Tables

destroy drops a table by name. Without if exists, dropping a name that doesn't resolve to a real table raises an error. Each statement targets exactly one table; drop several tables with several statements:

$entityManager->executeQuery("destroy SessionLog");

Temporary Tables

Add temporary when the target is a session-scoped table created with create temporary. An unqualified destroy already finds a same-named temporary table on its own, so temporary isn't strictly required — but writing it removes any ambiguity about which table is meant, and is recommended when you know the target is temporary:

$entityManager->executeQuery("destroy temporary StagingTotals");

if exists

Append if exists to make destroy a no-op instead of an error for a missing table:

$entityManager->executeQuery("destroy SessionLog if exists");

Altering Tables

alter Name (op {, op}) changes an existing table's shape. Each parenthesized, comma-separated entry is a single-purpose sub-operation — a rename, a retype, or a constraint change, each kept distinct:

$entityManager->executeQuery("
    alter Employee (
        add department = string(100),
        drop legacy_code,
        rename hire_date to hired_at,
        retype hired_at = datetime
    )
");

$entityManager->executeQuery("
    alter Employee (
        drop primary key,
        primary key (employee_no),
        add unique index employees_email_uniq (email),
        drop index employees_legacy_idx
    )
");

$entityManager->executeQuery("
    alter Payment (
        add foreign key (invoice_id) references Invoice (id) on delete cascade
    )
");

$entityManager->executeQuery("
    alter Payment (
        drop foreign key (invoice_id)
    )
");

Backfilling Added Columns

Adding a NOT NULL column to a table that already has rows normally fails — the engine has no value to put in the new column for rows that already exist. A trailing backfill 'value' clause on add solves this by populating existing rows with a literal value as part of the same operation:

$entityManager->executeQuery("
    alter Order (
        add status = string(20) backfill 'pending'
    )
");

The value is always written as a quoted string literal, whatever the column's actual type — backfill '0' for an integer column, not backfill 0. Under the hood, the column is added with a transient DEFAULT so existing rows pick it up, then the default is dropped again immediately — it exists only to seed old rows, not as a second, persisted source of truth alongside an entity's own @Orm\Column(default=...).

make:migrations (see Sculpt) generates this clause automatically when it detects a new non-nullable column being added to a table that already has rows, using the entity's declared @Orm\Column(default=...) as the backfill value. If the entity declares no default, generation fails with an error telling you to add one or write the migration by hand.

Transactional DDL

On platforms that support transactional DDL — pgsql, sqlite, sqlsrv — the statements compiled from a single create or alter call run wrapped in one transaction: if any statement in the sequence fails (say, a trailing embedded index entry, after the table itself was already created), everything already applied for that call rolls back. mysql/mariadb commit each DDL statement as it runs, so on those engines a failed alter call can leave its earlier sub-operations applied. create handles this case by dropping the table it just created on failure (unless if not exists matched an already-existing table), simulating a rollback on engines that don't provide one natively. Standalone index/destroy ... on statements always run outside a transaction, on every dialect.

Creating Indexes

index creates a single index on any table by name, whether entity-mapped or ad hoc — the compiler resolves it purely by name. The keyword order reads as index on Table is name (cols):

$entityManager->executeQuery("index on AuditEvent is audit_event_created_idx (created_at)");

Add unique right after index for a uniqueness constraint, and list more than one column for a composite index:

$entityManager->executeQuery("index unique on Products is products_sku_uniq (sku)");

$entityManager->executeQuery("index unique on Products is products_tenant_sku_idx (tenant_id, sku)");

Fulltext Indexes

fulltext occupies the same slot as unique — at most one of the two may be given, since no target dialect has a "unique fulltext" concept:

$entityManager->executeQuery("index fulltext on ArticleEntity is article_body_fulltext_idx (title, body)");

What this compiles to is materially different per dialect, though the QUEL syntax above is identical everywhere:

Destroying Indexes

destroy Name on Table drops a single index — the trailing on Table clause is what distinguishes an index destroy from the table form above; no separate keyword is needed:

$entityManager->executeQuery("destroy audit_event_created_idx on AuditEvent");

// No-op instead of an error if the index doesn't exist
$entityManager->executeQuery("destroy audit_event_created_idx on AuditEvent if exists");

Like destroy for tables, this targets exactly one index per statement — no comma-separated list. Without if exists, an unknown index name surfaces the underlying engine's own error rather than a synthetic one.

Destroying a fulltext index uses this same statement on every dialect. On mysql/mariadb/pgsql a fulltext index is a real, named index, so it drops exactly like a plain one. On sqlsrv/sqlite — where a fulltext index isn't an ordinary named object — ObjectQuel resolves the name automatically (via the extended property tag and FTS5 virtual table lookup described above) before dropping it; a name that doesn't resolve falls through to the same "unknown index" error a typo would produce anywhere else.

Scope & Limitations

• No default values on create's or alter's column definitions — a column with no value supplied on append must be declared nullable, or the statement is rejected.
• Foreign keys are single-column on both sides — no composite foreign keys, matching @Orm\ForeignKey's own limit.
• alter targets exactly one table per statement, and each sub-operation is single-purpose — renaming and retyping a column are always separate operations.
• create/alter/destroy/index aren't restricted to non-entity-mapped tables, but using them against a table an entity class also manages bypasses migrations entirely — not recommended.