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 modifying tables and their properties - ALTER TABLE, but 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 NOT EXISTS ] @name
		[ DROP ]
		[ SCHEMAFULL | SCHEMALESS ]
		[ PERMISSIONS [ NONE | FULL
			| FOR select @expression
			| FOR create @expression
			| FOR update @expression
			| FOR delete @expression
		] ]
    [ COMMENT @string ]
]

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;