DEFINE FIELD

The DEFINE FIELD statement allows you to instantiate a named field on a table, enabling you to set the field's schema and configuration.

The DEFINE FIELD statement allows you to instantiate a named field on a table, enabling you to set the field's data type, set a default value, apply assertions to protect data consistency, and set permissions specifying what operations can be performed on the field.

Requirements

• You must be authenticated as a root owner or editor, namespace owner or editor, or database owner or editor before you can use the DEFINE FIELD statement.
• You must select your namespace and database before you can use the DEFINE FIELD statement.

Statement syntax

Regular Field SyntaxComputed Field SyntaxRailroad Diagram (Regular)Railroad Diagram (Computed)

Regular fields

SurrealQL Syntax

DEFINE FIELD [ OVERWRITE | IF NOT EXISTS ] @name ON [ TABLE ] @table
    [ TYPE @type | object [ FLEXIBLE ] ]
    [ REFERENCE\
        [ ON DELETE REJECT |\
        ON DELETE CASCADE |\
        ON DELETE IGNORE |\
        ON DELETE UNSET |\
        ON DELETE THEN @expression ]\
    ]
    [ DEFAULT [ALWAYS] @expression ]
  [ READONLY ]
    [ VALUE @expression ]
    [ ASSERT @expression ]
    [ PERMISSIONS [ NONE | FULL\
        | FOR select @expression\
        | FOR create @expression\
        | FOR update @expression\
    ] ]
  [ COMMENT @string ]

Computed fields

A COMPUTED field is one that is not stored but computed every time it is accessed. Such fields have a more limited set of clauses that can be used.

SurrealQL Syntax

DEFINE FIELD [ OVERWRITE | IF NOT EXISTS ] @name ON [ TABLE ] @table
    COMPUTED @expression
    [ TYPE @type | object [ FLEXIBLE] ]
    [ PERMISSIONS [ NONE | FULL\
        | FOR select @expression\
        | FOR create @expression\
        | FOR update @expression\
    ] ]
  [ COMMENT @string ]

Example usage

The following expression shows the simplest way to use the DEFINE FIELD statement.

-- Declare the name of a field.
DEFINE FIELD email ON TABLE user;

Non-unicode fields can be defined and set using backticks where necessary. Be sure that any periods to indicate nested fields are not inside the backticks, as anything enclosed in backticks will be treated as a literal string.

DEFINE FIELD name.first    ON user TYPE string;
DEFINE FIELD `nómine`.prim ON user TYPE string;
DEFINE FIELD `nómine.prim` ON user TYPE string;

Defining data types

The DEFINE FIELD statement allows you to set the data type of a field. For a full list of supported data types, see Data types.

Simple data types

-- Set a field to have the string data type
DEFINE FIELD email ON TABLE user TYPE string;
-- Set a field to have the datetime data type
DEFINE FIELD created ON TABLE user TYPE datetime;
-- Set a field to have the bool data type
DEFINE FIELD locked ON TABLE user TYPE bool;
-- Set a field to have the number data type
DEFINE FIELD login_attempts ON TABLE user TYPE number;

Using the DEFAULT clause to set a default value

You can set a default value for a field using the DEFAULT clause. The default value will be used if no value is provided for the field.

-- A user is not locked by default.
DEFINE FIELD locked ON TABLE user TYPE bool
-- Set a default value if empty
  DEFAULT false;

Asserting rules on fields

You can take your field definitions even further by using asserts. Assert can be used to ensure that your data remains consistent.

-- Give the user table an email field. Store it in a string
DEFINE FIELD email ON TABLE user TYPE string
  -- Check if the value is a properly formatted email address
  ASSERT string::is_email($value);

Using regex to validate a string

You can use the ASSERT clause to apply a regular expression to a field to ensure that it matches a specific pattern.

-- Specify a field on the user table
DEFINE FIELD countrycode ON user TYPE string
    -- Ensure country code is ISO-3166
    ASSERT $value = /[A-Z]{3}/
    -- Set a default value if empty
    VALUE $value OR $before OR 'GBR'
;

Making a field READONLY

The READONLY clause can be used to prevent any updates to a field. This is useful for fields that are automatically updated by the system.

DEFINE FIELD created ON resource VALUE time::now() READONLY;