ALTER
statementAvailable since: v2.0.0
The ALTER
statement can be used to change definitions for defined resources.
NoteCurrently, the
ALTER
statement only supports modifying tables and their properties -ALTER TABLE
, but will be expanded to include other resources. In the meantime, theOVERWRITE
clause can be used in otherDEFINE
statements to redefine resources that have already been defined.
SurrealQL SyntaxALTER [ | 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 ] ]
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.
NoteTo see the already defined items that can be modified with
ALTER
, use the INFO statement (INFO FOR ROOT
,INFO FOR NAMESPACE
, orINFO 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;
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;
IF EXISTS
clauseYou 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;