ALTER FIELD

The ALTER FIELD statement is used to change or entirely drop clauses of a defined field on a table.

Statement syntax

ALTER FIELD [ IF EXISTS ] @name ON [ TABLE ] @table 
[ 
    DROP TYPE |
    DROP SQL TYPE |
    DROP FLEXIBLE |
    DROP READONLY |
    DROP INLINE |
    DROP VALUE |
    DROP ASSERT |
    DROP DEFAULT |
    DROP COMMENT |
    DROP REFERENCE |
    FLEXIBLE |
    READONLY |
    INLINE |
    REFERENCE |
    TYPE @type |
    SQL TYPE [ POSTGRESQL | MYSQL | DYNAMODB ] @string |
    VALUE @value |
    ASSERT @expression |
    DEFAULT [ ALWAYS ] @expression |
    [ PERMISSIONS [ NONE | FULL
		| FOR select @expression
		| FOR create @expression
		| FOR update @expression
		| FOR delete @expression
	] ]
    COMMENT @string |
    MIGRATE EXISTING ROWS AS @name USING @expression [ FALLBACK @value ]
]

Examples

As ALTER FIELD contains the same clauses available in a DEFINE FIELD statement, be sure to see that page for more examples.

Here is one example in which the name field is defined for a record user:

DEFINE FIELD name ON user TYPE string;

Later on, a database-wide parameter is defined to disallow certain user names. This can be followed up with an ALTER FIELD statement to add the ASSERT clause to it.

DEFINE PARAM $DISALLOWED_NAMES VALUE ["Lord British", "Lord Blackthorn"];
ALTER FIELD name ON user ASSERT $value NOT IN $DISALLOWED_NAMES;
CREATE user SET name = "Lord British";

"Found 'Lord British' for field `name`, with record `user:yn4yttkg5w683q2937bq`, but field must conform to: $value NOTINSIDE $DISALLOWED_NAMES"

Migrating existing records

Available since: v4.0.0

MIGRATE EXISTING ROWS converts the values that records already hold when ALTER FIELD changes the field's type. In a database at compatibility level 4, a SCHEMAFULL table that holds records refuses a type change without it unless the new type accepts every old value.

-- In a database at compatibility level 4
DEFINE TABLE person SCHEMAFULL;
DEFINE FIELD age ON person TYPE int;
CREATE person:1 SET age = 37;

ALTER FIELD age ON person TYPE string
    MIGRATE EXISTING ROWS AS age_to_string USING <string> $value FALLBACK '';
SELECT * FROM person;

[{ age: '37', id: person:1 }]

The rules for the transform, the FALLBACK value and the fields that cannot be migrated are in Migrating existing records.

Changing the declared SQL type

Available since: v4.0.0

SQL TYPE sets or replaces the declared SQL type of a field, and DROP SQL TYPE removes it. The new type applies to the writes that follow. Records that the table already holds are not checked when the type changes, so a record that does not fit the new type fails its next write.

DEFINE TABLE customer SCHEMAFULL;
DEFINE FIELD name ON customer TYPE string
    SQL TYPE POSTGRESQL 'varchar(20)';

ALTER FIELD name ON customer SQL TYPE POSTGRESQL 'varchar(40)';
CREATE customer:2 SET name = 'Augusta Ada King, Countess of Lovelace';
//- [{ id: customer:2, name: 'Augusta Ada King, Countess of Lovelace' }]

ALTER FIELD name ON customer DROP SQL TYPE;
(INFO FOR TABLE customer).fields.name;
//- 'DEFINE FIELD name ON customer TYPE string PERMISSIONS FULL'

See also

• •

  DEFINE FIELD