REMOVE statement
The REMOVE statement is used to remove resources such as databases, tables, indexes, events and more. Similar to an SQL DROP statement.
Statement syntax
SurrealQL Syntax
REMOVE [
NAMESPACE [ IF EXISTS ] @name
| DATABASE [ IF EXISTS] @name
| USER [ IF EXISTS ] @name ON [ ROOT | NAMESPACE | DATABASE ]
| ACCESS [ IF EXISTS ] @name ON [ NAMESPACE | DATABASE ]
| EVENT [ IF EXISTS ] @name ON [ TABLE ] @table
| FIELD [ IF EXISTS ] @name ON [ TABLE ] @table
| INDEX [ IF EXISTS ] @name ON [ TABLE ] @table
| ANALYZER [ IF EXISTS ] @name
| FUNCTION [ IF EXISTS ] fn::@name
| PARAM [ IF EXISTS ] $@name
| TABLE [ IF EXISTS ] @name
]
Example usage
Basic usage
The following queries show an example of how to remove resources.
REMOVE NAMESPACE surrealdb;
REMOVE DATABASE blog;
REMOVE USER writer ON NAMESPACE;
REMOVE USER writer ON DATABASE;
REMOVE ACCESS token ON NAMESPACE;
REMOVE ACCESS user ON DATABASE;
REMOVE EVENT new_post ON TABLE article;
REMOVE FIELD tags ON TABLE article;
REMOVE INDEX authors ON TABLE article;
REMOVE ANALYZER example_ascii;
REMOVE FUNCTION fn::update_author;
REMOVE PARAM $author;
REMOVE TABLE article;
Using if exists clause
The following queries show an example of how to remove resources using the IF EXISTS clause, which will only remove the resource if it exists.
REMOVE NAMESPACE IF EXISTS surrealdb;
REMOVE DATABASE IF EXISTS blog;
REMOVE USER IF EXISTS writer ON NAMESPACE;
REMOVE USER IF EXISTS writer ON DATABASE;
REMOVE ACCESS IF EXISTS token ON NAMESPACE;
REMOVE ACCESS IF EXISTS user ON DATABASE;
REMOVE EVENT IF EXISTS new_post ON TABLE article;
REMOVE FIELD IF EXISTS tags ON TABLE article;
REMOVE INDEX IF EXISTS authors ON TABLE article;
REMOVE ANALYZER IF EXISTS example_ascii;
REMOVE FUNCTION IF EXISTS fn::update_author;
REMOVE PARAM IF EXISTS $author;
REMOVE TABLE IF EXISTS article;
Usage in table views
Available since: v3.0.0
A table used as a source for a table view cannot be removed until the table view itself has been removed.
DEFINE TABLE pc;
DEFINE TABLE pc_agg AS SELECT count(), class FROM pc GROUP BY class;
CREATE |pc:3| SET class = "Wizard";
CREATE |pc:10| SET class = "Warrior";
SELECT * FROM pc_agg;
REMOVE TABLE pc;
REMOVE TABLE pc_agg;
REMOVE TABLE pc;
The SELECT * FROM pc_agg query shows that the table view is pulling data from the pc table. As long as the pc table exists, pc_agg cannot be removed.
[
{
class: 'Warrior',
count: 10,
id: pc_agg:['Warrior']
},
{
class: 'Wizard',
count: 3,
id: pc_agg:['Wizard']
}
]
'Invalid query: Cannot delete table `pc` on which a view is defined, table(s) `pc_agg` are defined as a view on this table.'
Behaviour after removal
While all REMOVE statements remove the definition for a resource, some resources have additional actions when removed. They are:
- REMOVE DATABASE: This effectively deletes the database by removing the index stores, deleting the definition, and clearing the cache.
- REMOVE NAMESPACE: Same as
REMOVE DATABASE, in addition to performing a remove on each database inside the namespace. - REMOVE TABLE: Similar to the two previous statements but on a single table, and will fail if a table view depends on it. Removing a table will also send a KILL notification for each live query defined on it.
- REMOVE INDEX: This statement also removes the index store cache and index data. If you are considering removing an index but want to test the behaviour out first, use an ALTER INDEX PREPARE REMOVE statement. This will decommission the index, after which you can test out queries to see their behaviour as they would function after the index is removed. If acceptable then the index can then be removed, or the change can be reverted by rebuilding the index.
Another REMOVE statement to note is REMOVE FIELD, as it does not remove any existing data. To remove the existing data, perform an UPDATE or UPSERT statement that uses UNSET on the field or sets the field’s value to NONE.
For a schemaless table, the existing data will remain present until unset.
DEFINE FIELD name ON person TYPE string;
CREATE person:one SET name = "Billy";
REMOVE FIELD name ON person;
SELECT * FROM person;
UPDATE person;
UPDATE person SET name = NONE;
For a schemafull table, read operations can be performed on a table that still contains data not defined in the schema. However, any updates will fail unless the field is unset to match the schema.
DEFINE TABLE person SCHEMAFULL;
DEFINE FIELD name ON person TYPE string;
CREATE person:one SET name = "Billy";
REMOVE FIELD name ON person;
SELECT * FROM person;
UPDATE person;
DEFINE FIELD created_at ON person TYPE datetime;
UPDATE person SET name = NONE, created_at = time::now();
