Data Definition Language

Use Cloud Spanner's Data Definition Language (DDL) to:

• Create a database.
• Create, alter, or drop tables in a database.
• Add, alter, or drop columns in a table.
• Create or drop indexes in a database.

DDL syntax

statement:
    { create_database | alter_database | create_table | create_index | alter_table | drop_table | drop_index }

create_database:
    CREATE DATABASE database_id

alter_database:
    ALTER DATABASE database_id
    action

where action is:
    SET OPTIONS ( optimizer_version = { 1 ...  2 | null },
                  version_retention_period = { 'duration' | null } )


create_table:
    CREATE TABLE ( [
       { column_name data_type [NOT NULL] [AS ( expression ) STORED] [ options_def ]
       | table_constraint }
       [, ... ]
    ] ) PRIMARY KEY ( [ column_name [ { ASC | DESC } ], ...] )
    [, INTERLEAVE IN PARENT table_name [ ON DELETE { CASCADE | NO ACTION } ] ]

    where data_type is:
        { scalar_type | array_type }

    and options_def is:
        { OPTIONS ( allow_commit_timestamp = { true | null } ) }

    and table_constraint is:
        [ CONSTRAINT constraint_name ]
        { CHECK ( expression ) |
          FOREIGN KEY ( column_name [, ... ] ) REFERENCES ref_table ( ref_column [, ... ] ) }


create_index:
    CREATE [ UNIQUE ] [ NULL_FILTERED ] INDEX index_name
    ON table_name ( key_part [, ...] ) [ storing_clause ] [ , interleave_clause ]

    where index_name is:
        {a—z|A—Z}[{a—z|A—Z|0—9|_}+]

    and key_part is:
        column_name [ { ASC | DESC } ]

    and storing_clause is:
        STORING ( column_name [, ...] )

    and interleave_clause is:
        INTERLEAVE IN table_name

alter_table:
    ALTER TABLE table_name
    action [, ... ]

    where action is:
        ADD [ COLUMN ] column_name data_type [ NOT NULL ] [AS ( expression ) STORED] [ options_def ]
        DROP [ COLUMN ] column_name
        ADD table_constraint
        DROP CONSTRAINT constraint_name
        SET ON DELETE { CASCADE | NO ACTION }
        ALTER [ COLUMN ] column_name { { data_type } [ NOT NULL ] | SET [ options_def ] }

    and data_type is:
        { scalar_type | array_type }

    and options_def is:
        { OPTIONS ( allow_commit_timestamp = { true | null } ) }

    and table_constraint is:
        [ CONSTRAINT constraint_name ]
        { CHECK ( expression ) |
          FOREIGN KEY ( column_name [, ... ] ) REFERENCES ref_table ( ref_column [, ... ] ) }

drop_table:
    DROP TABLE table_name

drop_index:
    DROP INDEX index_name

scalar_type:
    { BOOL | INT64 | FLOAT64 | NUMERIC | STRING( length ) | BYTES( length ) | DATE | TIMESTAMP }

length:
    { int64_value | MAX }

array_type:
    ARRAY< scalar_type >

int64_value:
    { decimal_value | hex_value }

decimal_value:
    [-]0—9+

hex_value:
    [-]0x{0—9|a—f|A—F}+

database_id:
    {a—z}[{a—z|0—9|_|-}+]{a—z|0—9}

table_name, column_name, index_name:
    {a—z|A—Z}[{a—z|A—Z|0—9|_}+]

Notation:

• Square brackets "[ ]" indicate optional clauses.
• Parentheses "( )" indicate literal parentheses.
• The vertical bar "|" indicates a logical OR.
• Curly braces "{ }" enclose a set of options.
• A comma followed by an ellipsis indicates that the preceding item can repeat in a comma-separated list. item [, ...] indicates one or more items, and [item, ...] indicates zero or more items.
• A comma "," indicates the literal comma.
• Angle brackets "<>" indicate literal angle brackets.
• An mdash "—" indicates a range of values between the items on either side of it.
• The plus sign "+" indicates that the preceding item can repeat.

Reserved keywords

Some words, like type names, are reserved in Cloud Spanner's DDL. If you need to use a reserved keyword as an identifier in your schema, enclose it in backticks (`). For the full list of reserved keywords in Cloud Spanner, see Lexical structure and syntax.

For example:

CREATE TABLE MyTable (
  RowId INT64 NOT NULL,
  `Int64` INT64
) PRIMARY KEY (RowId);

Naming conventions

The following rules apply to table, column, index, and foreign key constraint names.

• Must be at least 1 character long.

• Can contain a maximum of 128 characters.

• Must start with an uppercase or lowercase letter.

• Can contain uppercase and lowercase letters, numbers, and underscores, but not hyphens.

• No two Cloud Spanner objects can be created with the same name. This includes names that differ only in capitalization. For example, the second statement in the following snippet fails because the table names differ only by case.

  CREATE TABLE MyTable (col1 INT64) PRIMARY KEY (col1);
  CREATE TABLE MYTABLE (col1 INT64) PRIMARY KEY (col1);
  

• When referring to other schema objects in a DDL statement (for example, a column name for a primary key, or table and column names in an index), make sure to use the original case for the name of each entity. As an example, consider the table Singers created with the following statement.

  CREATE TABLE Singers (
    SingerId   INT64 NOT NULL,
    FirstName  STRING(1024),
    LastName   STRING(1024),
    SingerInfo BYTES(MAX),
    BirthDate  DATE,
  ) PRIMARY KEY(SingerId);
  

  The following command fails with the message Table not found: singers because it uses a different case for the Singers table.

  CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)
  

• Cloud Spanner schema object names are case insensitive in SQL queries. As an example, consider the table MyTable2 created with the following statement.

  CREATE TABLE MyTable2 (col1 INT64) PRIMARY KEY (col1);
  

  The following queries all succeed because schema object names are case-insensitive for queries.

  SELECT col1 FROM MyTable2 LIMIT 1
  SELECT COL1 FROM MYTABLE2 LIMIT 1
  SELECT COL1 FROM mytable2 LIMIT 1
  INSERT INTO MYTABLE2 (col1) VALUES(1)
  

DATABASE statements

CREATE DATABASE

When creating a Cloud Spanner database, you must provide a CREATE DATABASE statement, which defines the ID of the database:

CREATE DATABASE database_id

where database_id
    {a—z}[{a—z|0—9|_|-}+]{a—z|0—9}

Parameters

database_id

• The name of the database to create. See Naming Conventions.

ALTER DATABASE

Changes the definition of a table.

Syntax

ALTER DATABASE database_id
    action

where database_id is:
    {a—z}[{a—z|0—9|_|-}+]{a—z|0—9}

and action is:
    SET OPTIONS ( optimizer_version = { 1 ...  2 | null },
                  version_retention_period = { 'duration' | null } )

Description

ALTER DATABASE changes the definition of an existing database.

SET OPTIONS

• Use this clause to set an option at the database level of the schema hierarchy.

Parameters

database_id

• The name of the database whose attributes are to be altered. If the name is a reserved word or contains a hyphen, enclose it in backticks (`). For more information on database naming conventions, see Naming Conventions in this document.

options_def

• The optimizer_version = { 1 ... 2 | null } option allows you to specify the query optimizer version to use. By default, this is the latest version of the optimizer, but you can specify any available optimizer version. Setting this option to null is equivalent to setting it to the latest version, which is currently 2. For more information, see Query Optimizer.

• The version_retention_period = { 'duration' | null } is the period for which Cloud Spanner retains all versions of data and schema for the database. The duration must be in the range [1h, 7d] and can be specified in days, hours, minutes, or seconds. For example, the values 1d, 24h, 1440m, and 86400s are equivalent. Setting the value to null resets the retention period to the default, which is 1 hour. This option can be used for point-in-time recovery. For more information, see Point-in-time Recovery.

Data types

Scalars

The syntax for using a scalar type in DDL is:

{ BOOL | INT64 | FLOAT64 | NUMERIC | STRING( length ) | BYTES( length ) | DATE | TIMESTAMP }

length:
    { int64_value | MAX }

int64_value:
    { decimal_value | hex_value }

decimal_value:
    [-]0—9+

hex_value:
    [-]0x{0—9|a—f|A—F}+

An int64_value must correspond to an integer from -9,223,372,036,854,775,808 (-2⁶³) to 9,223,372,036,854,775,807 (2⁶³ − 1). It can be specified with decimal or hexadecimal notation. The hexadecimal form requires a 0x prefix, with a lowercase x.

STRING

STRING is a variable length Unicode character string. Its value must be a valid Unicode string. Length is required, and represents the maximum number of Unicode characters (not bytes) that can be stored in the field.

Notes:

• Writes to the column are rejected if the new value is not a valid Unicode string or exceeds the specified length.

• length can be an integer in the range [1, 2621440].

• For a field whose length is unpredictable or does not need to be constrained, you can set length to the convenience value MAX, which is equivalent to 2621440 for validation purposes.

  Only the actual length of the stored string impacts storage costs; specifying MAX does not use any additional storage capacity.

• Cloud Spanner requires Unicode strings to be UTF-8 encoded on receipt at the server.

• Collation is done by Unicode character numerical value (technically by code point, which is subtly different due to combining characters). For ASCII strings, this is the traditional sort order.

• You can reduce the length of a column after the table has been created, but doing so requires Cloud Spanner to validate that the existing data is within the length constraint.

BYTES

BYTES is a variable length binary string. Length is required, and represents the maximum number of bytes that can be stored in the field.

Notes:

• Writes to the column are rejected if the new value exceeds the specified length.

• length can be an integer in the range [1, 10485760] or the convenience value MAX, which is equivalent to 10485760 for validation purposes.

  Only the actual stored bytes impact storage costs; specifying MAX does not use any additional storage capacity.

• You can reduce the length of a column after the table has been created, but doing so requires Cloud Spanner to validate that the existing data is within the length constraint.

DATE

• A timezone-independent date.
• The range [0001-01-01, 9999-12-31] is the legal interval for dates. A write to a date column is rejected if the value is outside of that interval.
• See more information and the canonical format in Data Types.

TIMESTAMP

• A timestamp with nanosecond precision.
• Timezone-independent, over the range [0001-01-01 00:00:00 to 10000-01-01 00:00:00).
• See more information and the canonical format in Data Types.

Arrays

The syntax for using the ARRAY type in DDL is:

ARRAY< scalar_type >

Cloud Spanner supports arrays of scalars. The primary purpose of arrays is to store a collection of values in a space efficient way. Arrays are not designed to provide access to individual elements; to read or write a single element, you must read or write the entire array.

If your application uses data structures like vectors or repeated fields, you can persist their state in a Cloud Spanner array.

Here's an example of an alternate definition of Singers that uses multiple columns of ARRAY type:

CREATE TABLE Singers (
  SingerId INT64,
  FeaturedSingerIds ARRAY<INT64>,
  SongNames ARRAY<STRING(MAX)>,
) PRIMARY KEY (SingerId) ...;

Notes:

• Arrays with subtype ARRAY (nested arrays) are not supported.
• Arrays, like scalar values, can never be larger than 10 MiB total.
• Arrays can't be used as key columns.

• In a CREATE TABLE statement, you can create columns of ARRAY type with a NOT NULL annotation.

  After you create the table, you cannot add a column of ARRAY type with a NOT NULL annotation, and you cannot add a NOT NULL annotation to an existing column of ARRAY type.

TABLE statements

CREATE TABLE

Defines a new table.

Syntax

CREATE TABLE table_name ( [
   { column_name data_type [ NOT NULL ] [AS ( expression ) STORED] [ options_def ]
   | table_constraint }
   [, ... ]
] ) PRIMARY KEY ( [column_name [ { ASC | DESC } ], ...] )
[, INTERLEAVE IN PARENT table_name [ ON DELETE { CASCADE | NO ACTION } ] ]

where data_type is:
    { scalar_type | array_type }

and options_def is:
    { OPTIONS ( allow_commit_timestamp = { true | null } ) }

and table_constraint is:
    [ CONSTRAINT constraint_name ]
    { CHECK ( expression ) |
      FOREIGN KEY ( column_name [, ... ] ) REFERENCES  ref_table  ( ref_column [, ... ] ) }

Description

CREATE TABLE defines a new table in the current database.

Parameters

table_name

• The name of the table to be created. For naming guidance, see Naming conventions.

column_name

• The name of a column to be created. For naming guidance, see Naming conventions.

data_type

• The data type of the column, which can be a Scalar or an Array type.

NOT NULL

• This optional column annotation specifies that the column is required for all mutations that insert a new row.

• You cannot add a NOT NULL column to an existing table. For most column types, you can work around this limitation:

  • For columns of ARRAY type, the only time you can use a NOT NULL annotation is when you create the table. After that, you cannot add a NOT NULL annotation to a column of ARRAY type.

  • For all other column types, you can add a nullable column; fill that column by writing values to all rows; and update your schema with a NOT NULL annotation on that column.

AS ( expression ) STORED

• This clause creates a column as a generated column, which is a column whose value is defined as a function of other columns in the same row.

• expression can be any valid SQL expression that is assignable to the column data type with the following restrictions.

  • The expression can only reference columns in the same table.

  • The expression can't contain subqueries.

  • The expression can't contain non-deterministic functions such as PENDING_COMMIT_TIMESTAMP(), CURRENT_DATE(), and CURRENT_TIMESTAMP().

  • You can't modify the expression of a generated column.

• The STORED attribute following the expression causes the result of the function to be stored along with other columns of the table. Subsequent updates to any of the referenced columns causes the expression to be re-evaluated and stored.

• Generated columns without the STORED attribute are not allowed.

• Direct writes to generated columns are not allowed.

• Generated columns can't be used as, or part of, a primary key. They can, however, be secondary index keys.

• Column option allow_commit_timestamp is not allowed on generated columns, or any columns referenced by generated columns.

• You can't change the data type of a generated column, or any columns referenced by the generated column.

• You can't drop a column referenced by a generated column.

For examples on how to work with generated columns, see Creating and managing generated columns.

PRIMARY KEY ( [column_name[ { ASC | DESC } ], ...]

• Every table must have a primary key, and that primary key can be composed of zero or more columns of that table.

• Adding the DESC annotation on a primary key column name changes the physical layout of data from ascending order (default) to descending order.

    For more details, see Schema and data model.

[, INTERLEAVE IN PARENTtable_name[ ON DELETE { CASCADE | NO ACTION } ] ]

• This clause defines a child-to-parent table relationship, which results in a physical interleaving of parent and child rows. The primary-key columns of a parent must positionally match, both in name and type, a prefix of the primary-key columns of any child. Adding rows to the child table fails if the corresponding parent row does not exist. The parent row can either already exist in the database or can be inserted before the insertion of the child rows in the same transaction.

• The optional ON DELETE clause defines the behavior of rows in ChildTable when a mutation attempts to delete the parent row. The supported options are:

  • CASCADE: the child rows are deleted.

  • NO ACTION: the child rows are not deleted. If deleting a parent would leave behind child rows, thus violating parent-child referential integrity, the write will fail.

  You can omit the ON DELETE clause, in which case the default of ON DELETE NO ACTION is used.

    For more details, see Schema and data model.

CONSTRAINTconstraint_name

• An optional name for a table constraint. If a name is not specified, Cloud Spanner generates a name for the constraint. Constraints names, including generated names, can be queried from Cloud Spanner's information schema.

CHECK (expression)

• A CHECK constraint allows you to specify that the values of one or more columns must satisfy a boolean expression.

• expression can be any valid SQL expression that evaluates to a BOOL.

• The following restrictions apply to a check constraint expression term.

  • The expression can only reference columns in the same table.

  • The expression must reference at least one non-generated column, whether directly or through a generated column which references a non-generated column.

  • The expression can't reference columns that have set the allow_commit_timestamp option.

  • The expression can't contain subqueries.

  • The expression can't contain non-deterministic functions, such as CURRENT_DATE() and CURRENT_TIMESTAMP().

• For more information, see Creating and managing check constraints.

FOREIGN KEY (column_name[, ... ] ) REFERENCESref_table(ref_column[, ... ] )

• Use this clause to define a foreign key constraint. A foreign key is defined on the referencing table of the relationship, and it references the referenced table. The foreign key columns of the two tables are called the referencing and referenced columns, and their row values are the keys.

• A foreign key constraint requires that one or more columns of this table can only contain values that are in the referenced columns of the referenced table.

• When creating a foreign key, a unique constraint is automatically created on the referenced table, unless the entire primary key is referenced. If the unique constraint can't be satisfied, the entire schema change will fail.

• The number of referencing and referenced columns must be the same. Order is also significant. That is, the first referencing column refers to the first referenced column, the second to the second, and so on.

• The referencing and referenced columns must have matching types and they must support the equality operator ('='). The columns must also be indexable. Columns of type ARRAY and NUMERIC are not allowed.

• Foreign keys can't be created on columns with the allow_commit_timestamp=true option.

    For details, see Foreign keys.

OPTIONS ( allow_commit_timestamp = { true | null } )

• The allow_commit_timestamp option allows insert and update operations to request that Cloud Spanner write the commit timestamp of the transaction into the column. For details, see Commit timestamps.

ALTER TABLE

Changes the definition of a table.

Syntax

ALTER TABLE table_name
    action [, ... ]

where action is:
    ADD [ COLUMN  ] column_name data_type [ NOT NULL]  [AS ( expression ) STORED] [ options_def ]
    DROP [ COLUMN ] column_name
    ADD table_constraint
    DROP CONSTRAINT constraint_name
    SET ON DELETE { CASCADE | NO ACTION }
    ALTER [ COLUMN ] column_name { { data_type } [ NOT NULL ] | SET [ options_def ] }

and data_type is:
    { scalar_type | array_type }

and options_def is:
    { OPTIONS (allow_commit_timestamp = { true | null } ) }

and table_constraint is:
    [ CONSTRAINT constraint_name ]
    { CHECK ( expression ) |
      FOREIGN KEY ( column_name [, ... ] ) REFERENCES ref_table ( ref_column [, ... ] ) }

Description

ALTER TABLE changes the definition of an existing table.

ADD COLUMN

• Adds a new column to the table, using the same syntax as CREATE TABLE.

DROP COLUMN

• Drops a column from a table.

• You can't drop a column referenced by a generated column.

• Dropping a column referenced by a CHECK constraint is not allowed.

ADDtable_constraint

• Adds a new constraint to a table using the same syntax as CREATE TABLE.

• For foreign keys, the existing data is validated before the foreign key is added. If any existing constrained key does not have a corresponding referenced key, or the referenced key is not unique, the constraint is in violation, and the ALTER statement fails.

• For CHECK constraints, new data is validated immediately against the constraint. A long-running process is also started to validate the existing data against the constraint. If any existing data does not conform to the constraint, the check constraint is rolled back.

• The following restrictions apply to a check constraint expression term.

  • The expression can only reference columns in the same table.

  • The expression must reference at least one non-generated column, whether directly or through a generated column which references a non-generated column.

  • The expression can't reference columns that have set the allow_commit_timestamp option.

  • The expression can't contain subqueries.

  • The expression can't contain non-deterministic functions, such as CURRENT_DATE() and CURRENT_TIMESTAMP().

DROP CONSTRAINTconstraint_name

• Drops the specified constraint on a table, along with any associated index, if applicable.

SET ON DELETE { CASCADE | NO ACTION }

• This alteration can only be applied on child tables of parent-child, interleaved tables, relationships. For more information, see Schema and data model.

• The ON DELETE CASCADE clause signifies that when a row from the parent table is deleted, its child rows in this table will automatically be deleted as well. Child rows are all rows that start with the same primary key. If a child table does not have this annotation, or the annotation is ON DELETE NO ACTION, then you must delete the child rows before you can delete the parent row.

ALTER COLUMN

• Changes the definition of an existing column on a table.

Parameters

table_name

• The name of an existing table to alter.

column_name

• The name of a new or existing column. You can't change a table's key columns.

data_type

• Data type of the new column, or new data type for an existing column.

• You can't change the data type of a generated column, or any columns referenced by the generated column.

• Changing the data type is not allowed on any columns referenced in a CHECK constraint. options_def

• The (allow_commit_timestamp=true) option allows insert and update operations to request that Cloud Spanner write the commit timestamp of the transaction into the column. For details, see Commit timestamps.

table_constraint

• New table constraint for the table.

constraint_name

• The name of a new or existing constraint.

ref_table

• The referenced table in a foreign key constraint.

ref_column

• The referenced column in a foreign key constraint.

DROP TABLE

Removes a table.

Syntax

DROP TABLE table_name

Description

Use the DROP TABLE statement to remove a table from the database.

• DROP TABLE is not recoverable.

• You can't drop a table if there are indexes over it, or if there are any tables or indexes interleaved within it.

• A DROP TABLE statement automatically drops the table's foreign keys and foreign keys backing indexes.

Parameters

table_name

• The name of the table to drop.

INDEX statements

CREATE INDEX

Use the CREATE INDEX statement to define secondary indexes.

Syntax

CREATE [ UNIQUE ] [ NULL_FILTERED ] INDEX index_name
ON table_name ( key_part [, ...] ) [ storing_clause ] [ , interleave_clause ]

where index_name is:
    {a—z|A—Z}[{a—z|A—Z|0—9|_}+]

and key_part is:
    column_name [ { ASC | DESC } ]

and storing_clause is:
    STORING ( column_name [, ...] )

and interleave_clause is:
    INTERLEAVE IN table_name

Description

Cloud Spanner automatically indexes each table's primary key columns.

You can use CREATE INDEX to create secondary indexes for other columns. Adding a secondary index on a column makes it more efficient to look up data in that column. For more details, see secondary indexes.

Parameters

UNIQUE

• Indicates that this secondary index enforces a UNIQUE constraint on the data being indexed. The UNIQUE constraint causes any transaction that would result in a duplicate index key to be rejected. See Unique Indexes for more information.

NULL_FILTERED

• Indicates that this secondary index does not index NULL values. See Indexing of NULL values for more information.

index_name

• The name of the index to be created. For naming guidance, see Naming conventions.

table_name

• The name of the table to be indexed.

INTERLEAVE IN

• Defines a table to interleave the index in. If T is the table into which the index is interleaved, then:

  • T must be a parent of the table being indexed, and
  • The primary key of T must be the key prefix of the index.

  When should you create an interleaved index? If the index key that you want to use for index operations matches the key of a table, you might want to interleave the index in that table if the row in the table should have a data locality relationship with the corresponding indexed rows.

  For example, if you want to index all rows of Songs for a particular row of Singers, your index keys would contain SingerId and SongName and your index would be a good candidate for interleaving in Singers if you frequently fetch information about a singer as you fetch that singer's songs from the index. The definition of SongsBySingerSongName in Creating a Secondary Index is an example of creating such an interleaved index.

  Like interleaved tables, entries in an interleaved indexes are stored with the corresponding row of the parent table. See database splits for more details.

DESC

• Defines descending scan order for the corresponding index column. When scanning a table using an index column marked DESC, the scanned rows appear in the descending order with respect to this index column. If you don't specify a sort order, the default is ascending (ASC).

STORING

• Provides a mechanism for duplicating data from the table into one or more secondary indexes on that table. At the cost of extra storage, this can reduce read latency when looking up data using a secondary index, because it eliminates the need to retrieve data from the main table after having found the desired entries in the index. See STORING clause for an example.

DROP INDEX

Removes a secondary index.

Syntax

DROP INDEX index_name

Description

Use the DROP INDEX statement to drop a secondary index.

Parameters

index_name

• The name of the index to drop.

The following rules apply to database ids.

• Must start with a lowercase letter.
• Can contain lowercase letters, numbers, underscores, and hyphens, but not uppercase letters.
• Cannot end with an underscore or hyphen.
• Must be enclosed in backticks (`) if it is a reserved word or contains a hyphen.
• Can be between 2-30 characters long.
• Cannot be changed after you create it.