# Alter table statements

[Home](/rezoom-sql/master.md) > [Language](/rezoom-sql/language.md) > Alter table statements

[← Create view statements](/rezoom-sql/language/createviewstmt.md) | [Drop object statements →](/rezoom-sql/language/dropstmt.md)

## Alter Table Statements

An **alter table** statement is used to edit the schema of a table.

#### *alter-table*

![](/files/cvCj7SP7028FWFJ0wsfq)

### Core features

You can rename a table with:

```sql
ALTER TABLE MyTableOldName
RENAME TO MyTableNewName
```

Or add columns, like:

```sql
ALTER TABLE MyTable
ADD COLUMN MyNewColumn int null references SomeOtherTable(SomeOtherColumn)
```

**On SQLite, that's all you can do**. This is just a fact of life: SQLite doesn't support dropping columns, adding or removing constraints for existing columns, or editing column type affinity. For all of those things, in SQLite you must create a new table and copy your data over.

But on other database backends like `tsql` and `postgres`, you have more options.

### Constraints

You can add UNIQUE, PRIMARY KEY, FOREIGN KEY, or CHECK constraints to a table:

```sql
ALTER TABLE MyTable
ADD UNIQUE(Column1, Column2);

ALTER TABLE MyTable
ADD CONSTRAINT MyCheckStuff CHECK(Column1 > 0);
```

You can also remove these constraints.

```sql
ALTER TABLE MyTable
DROP CONSTRAINT MyTable_Column1_Column2_UNIQUE;

ALTER TABLE MyTable
DROP CONSTRAINT MyCheckStuff;
```

Note that when a constraint is not explicitly named when it is created, RZSQL automatically generates a name for it. This allows you to reliably predict the name regardless of your backend.

The below table shows how constraint names are generated for different constraint types:

| Constraint                                    | Name                                  | Notes                          |
| --------------------------------------------- | ------------------------------------- | ------------------------------ |
| primary key(col1, col2)                       | TableName\_col1\_col2\_PK             | ASC/DESC don't affect the name |
| unique(col1, col2)                            | TableName\_col1\_col2\_UNIQUE         | ASC/DESC don't affect the name |
| check(expr)                                   | TableName\_CHECK                      | `expr` doesn't affect the name |
| foreign key(a, b) references OtherTable(c, d) | TableName\_a\_b\_FK\_OtherTable\_c\_d |                                |

### Default values

You can add a default value for a column like so:

```sql
ALTER TABLE MyTable
ADD DEFAULT FOR MyColumn ('this is the default value');
```

You cannot add a default value for a column that already has one. If you want to change the default value for a column, you should drop the current default value like so, then add a new one:

```sql
ALTER TABLE MyTable
DROP DEFAULT FOR MyColumn;
```

Notice that, although default values are modeled as constraints on some backends, it is not possible to specify a custom constraint name for a default value.

This also means you don't need to know the constraint name to drop the default value, just the name of the column you want to affect.

### Column type, nullability, and collation

Different backends model attributes of columns in different ways. In T-SQL, nullability is part of the column's type, while in Postgres, it's a special kind of constraint.

In order to simplify working with different backends, RZSQL model's changes to column type, nullability, and collation with separate statements. It is not possible to change both type and nullability in a single `ALTER TABLE` statement unless you use a [vendor statement](/rezoom-sql/language/vendorstatements.md).

You change column type like so:

```sql
ALTER TABLE MyTable
    ALTER COLUMN MyColumn string(80);
```

You specify a [*type-name*](/rezoom-sql/language/datatypes.md#type-name) just like when adding a new column, but you can't specify anything else. The nullability and collation the column already had will be preserved.

To change the nullability of a column simply use `NULL` or `NOT NULL` instead of a type name.

```sql
-- allow nulls for MyColumn
ALTER TABLE MyTable
    ALTER COLUMN MyColumn NULL;

-- whoops, changed my mind: don't allow nulls
ALTER TABLE MyTable
    ALTER COLUMN MyColumn NOT NULL;
```

Finally, to change the collation of a column, use a `COLLATE` clause instead of a type name. The nullability and type of the column will be preserved.

It is up to you to be aware of the valid collation names for your database backend. RZSQL **does not check collation names**.

```sql
ALTER TABLE MyTable
    ALTER COLUMN MyColumn COLLATE SQL_Latin1_General_CP1_CI_AS;
```

### What if I need more?

Your database likely supports more advanced `ALTER TABLE` scenarios than this. By using [vendor statements](/rezoom-sql/language/vendorstatements.md) you can run whatever backend-specific code you want, and tell RZSQL to pretend you're doing something simpler.

For example, in T-SQL, when you add a nullable column with a default value, you can specify `WITH VALUES` to have the default value used instead of `NULL` for all existing rows. RZSQL doesn't support this, so you can fake it with a vendor statement:

```sql
vendor tsql {
    -- this is what actually runs on the DB...
    alter table Foo
        add NewColumn int
            constraint Foo_NewColumn_DEFAULT_CONSTRAINT
            default(0) with values;
} imagine {
    -- ...this is what RZSQL typechecks and uses to understand
    -- the change you're making to the model.
    alter table Foo
        add NewColumn int null default(0);
}
```

***

[← Create view statements](/rezoom-sql/language/createviewstmt.md) | [Drop object statements →](/rezoom-sql/language/dropstmt.md)


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://humbobst.gitbook.io/rezoom-sql/language/altertablestmt.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.