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.