Previous Next JavaScript must be enabled to correctly display this content

## Modify Table Definitions

Use `ALTER TABLE` statements to either add new fields to a table definition, or delete a currently existing field definition. You can also use an `ALTER TABLE` statement to change the default Time-to-Live (TTL) value for a table, and to add an IDENTITY column to a table.

### ALTER TABLE ADD field

To add a field to an existing table, use the `ADD` statement:

    ALTER TABLE table-name (ADD field-definition)

See Field Definitions for a description of what should appear in field-definitions, above. For example:

    ALTER TABLE Users (ADD age INTEGER)

You can also add fields to nested records. For example, if you have the following table definition:

    CREATE TABLE u (id INTEGER,
                    info record(firstName String)),
                    PRIMARY KEY(id))

then you can add a field to the nested record by using dot notation to identify the nested table, like this:

    ALTER TABLE u(ADD info.lastName STRING)

### ALTER TABLE DROP Option

To delete a field from an existing table, use the `DROP` option:

    ALTER TABLE table-name (DROP field-name)

For example, to drop the `age` field from the `Users` table:

    ALTER TABLE Users (DROP age)

Note:

You cannot drop a field if it is the primary key, or if it participates in an index.

You can also us the `ALTER TABLE MODIFY FIELD` clause to add, drop, or modify an IDENTITY column in a table.

### ALTER TABLE USING TTL

To change the default Time-to-Live (TTL) value for an existing table, use the `USING TTL` statement:

    ALTER TABLE table-name USING TTL ttl

For example:

    ALTER TABLE Users USING TTL 4 days

In case of MR Tables, you can not use the `USING TTL` clause along with the `IN REGIONS` clause. That is, you can not alter an MR table's TTL value and regions in a single statement.

For more information on the `USING TTL` statement, see USING TTL.

### ALTER TABLE ADD REGIONS

The `add regions` clause lets you link an existing multi-region table (MR Table) with new regions in a multi-region Oracle NoSQL Database environment.

The `add regions` clause is used in expanding MR Tables to new regions. See Use Case 2: Expand a Multi-Region Table in the Administrator's Guide.

To add a region to an existing MR Table, use the `ADD REGIONS` option:

    ALTER TABLE <table name> ADD REGIONS <comma separated list of regions>

See Add New Region to Existing Regions for the example code.

### ALTER TABLE DROP REGIONS

The `drop regions` clause lets you disconnect an existing multi-region table (MR Table) from a participating region in a multi-region Oracle NoSQL Database environment.

The `drop regions` clause is used in contracting MR Tables to fewer regions. See Use Case 3: Contract a Multi-Region Table in the Administrator's Guide.

To drop a region from an MR Table, use the `DROP REGIONS` option:

    ALTER TABLE <table name> DROP REGIONS <comma separated list of regions>

See Alter the MR Table to Drop Regions for the example code.