# ALTER TABLE

> The schema changes ALTER TABLE makes — columns, attribute groups, defaults, nullability, names and types — and how running readers follow them.

Every `ALTER TABLE` creates a new schema generation of the table. Existing rows
are not rewritten: they are read by column identity, so a column added later
reads as its introduction value (its default, or NULL) on older rows. Readers of
the table, including running transformations, keep working across changes.

## Actions

| Action | Form |
| --- | --- |
| add a column | `ALTER TABLE <t> ADD [COLUMN] <column definition>` |
| drop a column | `ALTER TABLE <t> DROP [COLUMN] <c>` |
| change a column type | `ALTER TABLE <t> ALTER [COLUMN] <c> TYPE <type>` |
| add an attribute group | `ALTER TABLE <t> ADD ATTRIBUTE GROUP <g> (<column> <type> NULL, …) [VALID BY (…)] [REVISION BY (…)]` |
| rename a column | `ALTER TABLE <t> RENAME COLUMN <old> TO <new>` |
| rename a table | `ALTER TABLE <t> RENAME TO <new>` |
| set a default | `ALTER TABLE <t> ALTER [COLUMN] <c> SET DEFAULT <literal>` |
| remove a default | `ALTER TABLE <t> ALTER [COLUMN] <c> DROP DEFAULT` |
| make a column nullable | `ALTER TABLE <t> ALTER [COLUMN] <c> DROP NOT NULL` |

One statement carries one action. The column definition of `ADD COLUMN` takes
the same options as in [CREATE TABLE](/docs/sql/create-table).

## Adding columns

```sql
ALTER TABLE transfers ADD COLUMN fee UINT256 NULL;
```

```sql
ALTER TABLE balances ADD COLUMN label STRING NOT NULL DEFAULT 'unlabelled';
```

Every existing row reads the new column at its introduction value: NULL for
`fee`, `'unlabelled'` for `label`.

## Defaults and nullability

```sql
ALTER TABLE balances ALTER COLUMN label SET DEFAULT 'wallet';
```

```sql
ALTER TABLE balances ALTER COLUMN label DROP NOT NULL;
```

A new default applies to later writes; rows written before keep the value they
were written with.

## Renaming

```sql
ALTER TABLE transfers RENAME COLUMN fee TO network_fee;
```

Rows of every generation read under the new name, including reads
`AS OF COMMIT` an earlier commit.

## Adding attribute groups

```sql
ALTER TABLE balances ADD ATTRIBUTE GROUP labels (
  label_text STRING NULL,
  labelled_at DATETIME64(3, 'UTC') NULL
);
```

The group's columns are nullable. A new producer or transformation can own the
group at once. See [Attribute groups](/docs/concepts/attribute-groups).

## Changes under running transformations

A transformation that reads or writes a table is re-bound to each new schema
generation and keeps running. A table keeps up to 64 schema generations,
configurable with `node.resources.execution.schema_maximum_rungs`. See
[Limits](/docs/operations/limits).

---

Source: https://nyxdb.io/docs/sql/alter/