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 | create_table | create_index | alter_table | drop_table | drop_index } create_database: CREATE DATABASE database_id create_table: CREATE TABLE table_name ( [column_def, ...] ) primary_key [, cluster] column_def: column_name {scalar_type | array_type} [NOT NULL] [options_def] 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 > options_def: OPTIONS (allow_commit_timestamp = { true | null }) 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_column_alteration } table_alteration: { ADD COLUMN column_def | DROP COLUMN column_name | SET ON DELETE { CASCADE | NO ACTION } } table_column_alteration: ALTER COLUMN column_name { { scalar_type | array_type } [NOT NULL] | SET options_def } 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|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);
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 database_id: {a—z}[{a—z|0—9|_|-}+]{a—z|0—9}
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.
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|A—F}+
An int64_value
must correspond to an integer from -9,223,372,036,854,775,808
(-263) to 9,223,372,036,854,775,807 (263 − 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 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 are validated and will be 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] (2.5 MB).For a field whose length is unpredictable or does not need to be constrained, you can set
length
to the convenience valueMAX
, which is equivalent to 2621440 for validation purposes.Only the actual length of the stored string impacts storage costs; using
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 validated and rejected if the new value exceeds the specified length.
length
can be an integer in the range [1, 10485760] (10 MB) or the convenience valueMAX
, which is equivalent to 10485760 for validation purposes.Only the actual stored bytes impact storage costs; using
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 ofARRAY
type with aNOT NULL
annotation.After you create the table, you cannot add a column of
ARRAY
type with aNOT NULL
annotation, and you cannot add aNOT NULL
annotation to an existing column ofARRAY
type.
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] [options_def] 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|_}+] options_def: OPTIONS (allow_commit_timestamp = { true | null })
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. 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 it can be added earlier in the same transaction that adds a row to the child table.The optional
ON DELETE
clause defines the behavior of rows inChildTable
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 ofON 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. For most column types, you can work around this limitation:- For columns of
ARRAY
type, the only time you can use aNOT NULL
annotation is when you create the table. After that, you cannot add aNOT NULL
annotation to a column ofARRAY
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.
- For columns of
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
andMyTable
in the same database or columns namesmycolumn
andMyColumn
in the same table.
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
Use the ALTER TABLE
statement to change table definitions.
ALTER TABLE table_name { table_alteration | table_column_alteration } table_alteration: { ADD COLUMN column_def | DROP COLUMN column_name | SET ON DELETE { CASCADE | NO ACTION } } table_column_alteration: ALTER COLUMN column_name { { scalar_type | array_type } [NOT NULL] | SET options_def } options_def: OPTIONS (allow_commit_timestamp = { true | null })
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.
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 aUNIQUE
constraint on the data being indexed. TheUNIQUE
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 indexNULL
values. See Indexing of NULL values for more information.INTERLEAVE IN
defines a table to interleave the index in. IfT
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 ofSingers
, your index keys would containSingerId
andSongName
and your index would be a good candidate for interleaving inSingers
if you frequently fetch information about a singer as you fetch that singer's songs from the index. The definition ofSongsBySingerSongName
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 markedDESC
, 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
andMyIndex
in the same database.
DROP INDEX
Use the DROP INDEX
statement to drop a secondary index.
DROP INDEX index_name