Data Definition Language

Use Cloud Spanner's Data Definition Language (DDL) to create, alter, or drop tables and indexes in a database.

DDL syntax

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

create_database:
    CREATE DATABASE database_name

create_table:
    CREATE TABLE table_name (
    [column_def, ...] )
    primary_key [, cluster]

column_def:
    column_name (scalar_type | array_type) [NOT NULL]

primary_key:
    PRIMARY KEY ( [key_part, ...] )

key_part:
    column_name [{ ASC | DESC }]

cluster:
    INTERLEAVE IN PARENT table_name [ ON DELETE { CASCADE | NO ACTION } ]

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

length:
    { int64_value | MAX }

array_type:
    ARRAY< scalar_type >

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

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

interleave_clause:
    INTERLEAVE IN table_name

alter_table:
    ALTER TABLE table_name table_alteration

table_alteration:
    { ADD COLUMN column_def | ALTER COLUMN column_def | DROP COLUMN column_name |
      SET ON DELETE { CASCADE | NO ACTION } }

drop_table:
    DROP TABLE table_name

drop_index:
    DROP INDEX index_name

int64_value:
    { decimal_value | hex_value }

decimal_value:
    [-]0—9+

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

database_name:
    {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 within square brackets "[, ... ]" indicates that the preceding item can repeat in a comma-separated list.
  • 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 example:

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

DATABASE statements

CREATE DATABASE

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

CREATE DATABASE database_name

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

Database names:

  • 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.
  • Can be between 2-30 characters long.
  • Cannot be changed after you create it.

Data types

Scalars

The syntax for using a scalar type in DDL is:

{ BOOL | INT64 | FLOAT64 | 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}+

int64_values:

  • Can be in the range of -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.
  • Can be a hex digit, prepended with "0x" (case sensitive).

STRING

STRING is a variable length Unicode character string. Its value must be a valid Unicode strings. 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 will be rejected if the new value is not a valid Unicode string or exceeds the specified length.

  • 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 satisfies the length constraint.

  • length can be an integer in the range [1, 2621440] (2.5 mebichars)

  • For fields whose length is completely unpredictable, you can set length to the convenience value (MAX), which corresponds to the largest allowed length (2.5 mebichars).

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 will be rejected if the new value exceeds the specified length.

  • 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 satisfies the length constraint.

  • length can be an integer in the range [1, 10485760] (10 MiB) or the convenience value MAX.

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 individual element access; 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 easily 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 10MiB total.
  • Arrays can't be used as key columns.

TABLE statements

CREATE TABLE

Use the CREATE TABLE statement to define tables.

CREATE TABLE table_name(
[column_def, ...] )
primary_key [, cluster]

column_def:
    column_name (scalar_type | array_type) [NOT NULL]

primary_key:
    PRIMARY KEY ( [key_part, ...] )

key_part:
    column_name [{ ASC | DESC }]

cluster:
    INTERLEAVE IN PARENT table_name [ ON DELETE { CASCADE | NO ACTION } ]

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

Notes:

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

  • INTERLEAVE IN PARENT 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.

  • 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.

  • A NOT NULL annotation makes a column required for all mutations that insert a new row. You cannot add a NOT NULL column to an existing table. (However, as a workaround, you can add a nullable column, fill that column with writes to all rows, then update your schema with a NOT NULL annotation on that column.)

  • Table and column names:

    • Can be between 1-128 characters long.
    • Must start with an uppercase or lowercase letter.
    • Can contain uppercase and lowercase letters, numbers, and underscores, but not hyphens.
    • Are case-insensitive. For example, you cannot create tables named mytable and MyTable in the same database or columns names mycolumn and MyColumn in the same table.

ALTER TABLE

Use the ALTER TABLE statement to change table definitions.

ALTER TABLE table_name table_alteration

table_alteration:
    { ADD COLUMN column_def | ALTER COLUMN column_def | DROP COLUMN column_name |
      SET ON DELETE { CASCADE | NO ACTION } }

DROP TABLE

Use the DROP TABLE statement to drop tables. DROP TABLE is not recoverable.

DROP TABLE table_name

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 ]

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

interleave_clause:
    INTERLEAVE IN table_name

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

Notes:

  • 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 NULLs for more information.

  • 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.

  • Index names:

    • Can be between 1-128 characters long.
    • Must start with an uppercase or lowercase letter.
    • Can contain uppercase and lowercase letters, numbers, and underscores, but not hyphens.
    • Are case-insensitive. For example, you cannot create indexes named myindex and MyIndex in the same database.

DROP INDEX

Use the DROP INDEX statement to drop a secondary index.

DROP INDEX index_name

Send feedback about...

Cloud Spanner Documentation