ALTER statement

Available since: v2.0.0

The ALTER statement can be used to change the behaviour of database resources.

There are two main cases in which to use an ALTER statement:

• Modifying previously defined resources. This can currently be used to modify tables and fields. For other such modifications, use the OVERWRITE clause in other DEFINE statements.
• Modifying other resources using clauses not present in other DEFINE statements. Examples of this are the PREPARE REMOVE clause to prepare an index for removal, the COMPACT clause to compact the system/namespace/database/single table, and the QUERY_TIMEOUT clause to define or drop the query timeout for the entire datastore.

Some examples of ALTER statements are as follows. For further details, see the individual pages for each type of ALTER statement.

Modify a table schema

When starting a new project, you may require a table to be schemaless to allow for flexibility in the data structure. However, as the project progresses, you may want to lock down the schema to prevent new fields from being added.

An example of ALTER to modify an existing table:

DEFINE TABLE user SCHEMALESS;
DEFINE FIELD name ON TABLE user TYPE string;
CREATE user SET name = "LordofSalty";

-- Now make it schemafull to ensure that no other fields can be used
ALTER TABLE user SCHEMAFULL;

Modify table permissions

You can also use the ALTER statement to change a table’s permissions. An ALTER statement only needs to include the items to be altered, not the entire definition.

-- Will show up as DEFINE TABLE user TYPE ANY SCHEMAFULL PERMISSIONS NONE
DEFINE TABLE user SCHEMAFULL;

-- Now defined as DEFINE TABLE user TYPE ANY SCHEMAFULL PERMISSIONS FULL
ALTER TABLE user PERMISSIONS FOR create FULL;

Using IF EXISTS clause

You can use the’ IF EXISTS’ clause to prevent an error from occurring when trying to alter a table that does not exist.

ALTER TABLE IF EXISTS user SCHEMAFULL;