ALTER statement

Available since: v2.0.0

The ALTER statement can be used to change definitions for defined resources.

Note

Currently, the ALTER statement only supports a limited number of resources: tables, and indexes to prepare them for removal. ALTER will be expanded to include other resources. In the meantime, the OVERWRITE clause can be used in other DEFINE statements to redefine resources that have already been defined.

SurrealQL Syntax
ALTER [
	| TABLE [ IF EXISTS ] @name
		[ DROP ]
		[ SCHEMAFULL | SCHEMALESS ]
		[ PERMISSIONS [ NONE | FULL
			| FOR select @expression
			| FOR create @expression
			| FOR update @expression
			| FOR delete @expression
		] ]
    [ CHANGEFEED @duration ]
  | INDEX @name ON TABLE @table PREPARE REMOVE
    [ DROP ] [ COMMENT @string | CHANGEFEED ]
  ]

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. This can be done using the ALTER statement.

Note

To see the already defined items that can be modified with ALTER, use the INFO statement (INFO FOR ROOT, INFO FOR NAMESPACE, or INFO FOR DATABASE).

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;

ALTER INDEX PREPARE REMOVE

Available since: v3.0.0-alpha.17

As the name implies, an ALTER INDEX PREPARE REMOVE statement alters an index to prepare it for removal. This statement sets up a step in which the index has been decommissioned (prepared for removal), but not yet removed. At this point, SELECT queries along with the EXPLAIN clause to monitor query performance without the index.

-- 1. Decommission the index
ALTER INDEX my_index ON my_table PREPARE REMOVE;

-- 2. Monitor query performance and verify queries still work
SELECT ... FROM my_table EXPLAIN;

-- 3. If satisfied, permanently remove the index
REMOVE INDEX my_index ON my_table;

If removing the index is no longer desired, it can be restored to a useful state by using a REBUILD INDEX statement.

REBUILD INDEX my_index ON my_table;