Which of the following statements is best supported by the data in the table?

This feature is covered by the of the Google Cloud Terms of Service. Pre-GA features might have limited support, and changes to pre-GA features might not be compatible with other pre-GA versions. For more information, see the .

PostgreSQL interface note: The preview release of fine-grained access control doesn't support the PostgreSQL interface for Cloud Spanner.

PostgreSQL interface note: Spanner Vertex AI integration is not supported in PostgreSQL-dialect databases.

Use Google Standard SQL'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.
• Create, replace, or drop views in a database.
• Create or drop database roles.
• Grant privileges to database roles.
• Grant database roles to other database roles.
• Create, alter, or drop ML models in a database.

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.

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

  8 indicates one or more items, and

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

  9 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 have special meaning in the Google Standard SQL language and are reserved in its DDL. To use a reserved keyword as an identifier in your schema, enclose it in backticks (

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

For example:

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

Naming conventions

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 (

  CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)
  

  0) if it's a reserved word or contains a hyphen.
• Can be between 2-30 characters long.
• Cannot be changed after you create it.

The following rules apply to table, column, index, view, role, and constraint names.

• Must be at least one 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 Spanner objects can be created with the same name, including 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

  CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)
  

  2 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

  CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)
  

  3 because it uses a different case for the

  CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)
  

  2 table.

  CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)
  

• Schema object names are case insensitive in SQL queries. As an example, consider the table

  CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)
  

  5 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);
  

Data types

The following are the data types used in Google Standard SQL.

Scalars

The syntax for using a scalar type in DDL is:

{
  BOOL
  | INT64
  | FLOAT64
  | NUMERIC
  | STRING( length )
  | JSON
  | 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

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

STRING

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

Notes:

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

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

  0 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

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

  0 to the convenience value

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

  2, which is equivalent to 2621440 for validation purposes.

  Only the actual length of the stored string impacts storage costs; specifying

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

  2 does not use any additional storage capacity.

• Google Standard SQL 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 Spanner to that the existing data is within the length constraint.

JSON

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

See Working with JSON and for details.

BYTES

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

Notes:

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

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

  0 can be an integer in the range [1, 10485760] or the convenience value

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

  2, which is equivalent to 10485760 for validation purposes.

  Only the actual stored bytes impact storage costs; specifying

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

  2 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 Spanner to 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 .

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 .

Arrays

The syntax for using the

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

ARRAY< scalar_type >

Google Standard SQL 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 Google Standard SQL array.

Here's an example of an alternate definition of

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

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

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

Notes:

• Arrays with subtype

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

  9 (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

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

  3 statement, you can create columns of

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

  9 type with a

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

  5 annotation.

  After you create the table, you cannot add a column of

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

  9 type with a

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

  5 annotation, and you cannot add a

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

  5 annotation to an existing column of

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

  9 type.

DATABASE statements

CREATE DATABASE

When creating a Google Standard SQL database, you must provide a

{
  BOOL
  | INT64
  | FLOAT64
  | NUMERIC
  | STRING( length )
  | JSON
  | 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}+

CREATE DATABASE database_id

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

Parameters

{
  BOOL
  | INT64
  | FLOAT64
  | NUMERIC
  | STRING( length )
  | JSON
  | 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}+

• The name of the database to create. .

ALTER DATABASE

Changes the definition of a database.

Syntax

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

Description

{
  BOOL
  | INT64
  | FLOAT64
  | NUMERIC
  | STRING( length )
  | JSON
  | 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}+

{
  BOOL
  | INT64
  | FLOAT64
  | NUMERIC
  | STRING( length )
  | JSON
  | 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}+

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

Parameters

{
  BOOL
  | INT64
  | FLOAT64
  | NUMERIC
  | STRING( length )
  | JSON
  | 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}+

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

  CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)
  

  0). For more information on database naming conventions, see in this document.

{
  BOOL
  | INT64
  | FLOAT64
  | NUMERIC
  | STRING( length )
  | JSON
  | 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}+

• The

  {
    BOOL
    | INT64
    | FLOAT64
    | NUMERIC
    | STRING( length )
    | JSON
    | 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}+
  

  7 option allows you to specify the query optimizer version to use. Setting this option to

  {
    BOOL
    | INT64
    | FLOAT64
    | NUMERIC
    | STRING( length )
    | JSON
    | 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}+
  

  8 is equivalent to setting it to the default version. For more information, see Query Optimizer.

• The

  {
    BOOL
    | INT64
    | FLOAT64
    | NUMERIC
    | STRING( length )
    | JSON
    | 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}+
  

  9 option allows you to specify the query optimizer statistics package name to use. By default, this is the latest collected statistics package, but you can specify any available statistics package version. Setting this option to

  {
    BOOL
    | INT64
    | FLOAT64
    | NUMERIC
    | STRING( length )
    | JSON
    | 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}+
  

  8 is equivalent to setting it to the latest version. For more information, see Query statistics package versioning.

• The

  ARRAY< scalar_type >
  

  1 is the period for which Spanner retains all versions of data and schema for the database. The duration must be in the range

  ARRAY< scalar_type >
  

  2 and can be specified in days, hours, minutes, or seconds. For example, the values

  ARRAY< scalar_type >
  

  3,

  ARRAY< scalar_type >
  

  4,

  ARRAY< scalar_type >
  

  5, and

  ARRAY< scalar_type >
  

  6 are equivalent. Setting the value to

  {
    BOOL
    | INT64
    | FLOAT64
    | NUMERIC
    | STRING( length )
    | JSON
    | 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}+
  

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

• The

  ARRAY< scalar_type >
  

  8 sets the leader region for your database. You can only use this parameter for databases that use a multi-region configuration.

  ARRAY< scalar_type >
  

  9 must be set to

  {
    BOOL
    | INT64
    | FLOAT64
    | NUMERIC
    | STRING( length )
    | JSON
    | 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}+
  

  8, or one of the read-write replicas in your multi-region configuration.

  {
    BOOL
    | INT64
    | FLOAT64
    | NUMERIC
    | STRING( length )
    | JSON
    | 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}+
  

  8 resets the leader region to the default leader region for your database's multi-region configuration. For more information, see .

TABLE statements

CREATE TABLE

Defines a new table.

Syntax

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

Description

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

Parameters

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

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

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

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

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

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

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

• The name of a column of type

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

  7, that is also specified in the CREATE TABLE statement.

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

• The number of days after the date in the specified

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

  9, after which the row is marked for deletion. Valid values are non-negative integers.

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

• 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

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

    9 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

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

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

CREATE DATABASE database_id

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

CREATE DATABASE database_id

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

CREATE DATABASE database_id

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

• This clause sets a default value for the column.
• A column with a default value can be a key or non-key column.
• A column can't have a default value and also be a generated column.
• You can insert your own value into a column that has a default value, overriding the default value. You can also reset a non-key column to its default value by using

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

  6

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

  7

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

  8.
• A generated column or a check constraint can depend on a column with a default value.
• A column with a default value can't be a commit timestamp column.

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

    9 can't be used as a default value.

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

    00 is disallowed.

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

  4 can be a literal or any valid SQL expression that is assignable to the column data type, with the following properties and restrictions:
  • The expression can be non-deterministic.
  • The expression can't reference other columns.
  • The expression can't contain subqueries, query parameters, aggregates, or analytic functions.

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

CREATE DATABASE database_id

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

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

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

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

  4 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 , , and .

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

• The

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

  09 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

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

  09 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

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

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

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

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

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

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

• Adding the

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

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

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

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

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

• 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 exist in the database or be inserted before the insertion of the child rows in the same transaction.

• The optional

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

  19 clause defines the behavior of rows in

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

  20 when a mutation attempts to delete the parent row. The supported options are:

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

    21: the child rows are deleted.

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

    22: 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

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

  19 clause, in which case the default of

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

  24 is used.

    For more details, see Schema and data model.

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

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

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

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

CREATE DATABASE database_id

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

CREATE DATABASE database_id

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

• A

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

  30 constraint lets you specify that the values of one or more columns must satisfy a boolean expression.

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

  31 can be any valid SQL expression that evaluates to a

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

  32.

• The following restrictions apply to a check constraint

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

  31 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

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

    11 option.

  • The expression can't contain subqueries.

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

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

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

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

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

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

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

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

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

• 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 contain only 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

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

  9 are not allowed.

• Foreign keys can't be created on columns with the

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

  45 option.

    For details, see Foreign keys.

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

• The

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

  11 option allows insert and update operations to request that Spanner write the commit timestamp of the transaction into the column. For details, see Commit timestamps in Google Standard SQL-dialect databases.

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

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

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

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

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

• Use this clause to set a row deletion policy for this table. See Time to live (TTL) for details.

ALTER TABLE

Changes the definition of a table.

Syntax

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

Description

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

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

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

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

  3.

• You can specify

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

  5 in an

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

  57 statement if you specify

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

  3

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

  4

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

  5 or

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

  02

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

  4

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

  04 for the column.

• If you include

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

  3

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

  4

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

  5 or

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

  02

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

  4

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

  04, the expression is evaluated and the computed value is backfilled for existing rows. The backfill operation is asynchronous. This backfill operation happens only when an

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

  54 statement is issued. There's no backfill on

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

  71.

• The

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

  72 clause has restrictions. See the description of this clause in

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

  73.

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

• Drops a column from a table.

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

• Dropping a column referenced by a

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

  75 constraint is not allowed.

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

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

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

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

  78.

• 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

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

  79 statement fails.

• For

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

  30 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

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

  31 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

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

    11 option.

  • The expression can't contain subqueries.

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

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

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

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

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

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

• The

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

  88 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

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

  24, then you must delete the child rows before you can delete the parent row.

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

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

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

  5

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

  92

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

  4

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

  94

  • This clause changes the data type of the column and optionally sets a default value for the column.

  • The

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

    72 clause has restrictions. See the description of this clause in

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

    73.

  • Statements to set, change, or drop default value of an existing column do not affect existing rows.

  • If the column has data and is altered to have the

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

    5 constraint, the statement might fail if there is at least one existing row with a

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

    98 value. This is true even when a

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

    99 is specified, because there is no backfill operation for

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

    71.

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

  01

  • The

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

    02 clause is the only allowed option. If

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

    03, a commit timestamp can be stored into the column.

    To learn about commit timestamps, see Commit timestamps in Google Standard SQL-dialect databases.

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

  04

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

  05

  • Sets or changes a default value for the column. Only metadata is affected. Existing data is not changed.

  • This clause has restrictions. See the description of this clause in

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

    73.

  • When you use this clause, the expression result must be assignable to the current column type. To change the column type and default value in a single statement, use:

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

    53

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

    08

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

    71

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

    10

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

    72

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

    4

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

  13
  • Drops the column default value. Only metadata is affected. Existing data is not changed.

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

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

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

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

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

• Adds a row deletion policy to the table defining the amount of time after a specific date after which to delete a row. See Time to live. Only one row deletion policy can exist on a table at a time.

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

• Drops the row deletion policy on a table.

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

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

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

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

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

• Replaces the existing row deletion policy with a new policy.

Parameters

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

• The name of an existing table to alter.

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

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

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

• 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

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

  75 constraint.

  {
    BOOL
    | INT64
    | FLOAT64
    | NUMERIC
    | STRING( length )
    | JSON
    | 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}+
  

  6

• The

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

  30 option allows insert and update operations to request that Spanner write the commit timestamp of the transaction into the column. For details, see Commit timestamps in Google Standard SQL-dialect databases.

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

• New table constraint for the table.

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

• The name of a new or existing constraint.

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

• The referenced table in a foreign key constraint.

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

• The referenced column in a foreign key constraint.

DROP TABLE

Removes a table.

Syntax

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

Description

Use the

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

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

  35 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

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

  35 statement automatically drops the foreign keys and foreign keys backing indexes of a table.

Parameters

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

• The name of the table to drop.

INDEX statements

CREATE INDEX

Use the

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

Syntax

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

Description

Spanner automatically indexes the primary key columns of each table.

You can use

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

Parameters

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

• Indicates that this secondary index enforces a

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

  41 constraint on the data being indexed. The

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

  41 constraint causes any transaction that would result in a duplicate index key to be rejected. See for more information.

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

• Indicates that this secondary index does not index

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

  98 values. See for more information.

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

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

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

• The name of the table to be indexed.

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

• Defines a table to interleave the index in. If

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

  49 is the table into which the index is interleaved, then:

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

    49 must be a parent of the table being indexed, and
  • The primary key of

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

    49 must be the key prefix of the 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

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

  52 for a particular row of

  CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)
  

  2, your index keys would contain

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

  54 and

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

  55 and your index would be a good candidate for interleaving in

  CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)
  

  2 if you frequently fetch information about a singer as you fetch that singer's songs from the index. The definition of

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

  57 in is an example of creating such an interleaved index.

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

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

• Defines descending scan order for the corresponding index column. When scanning a table using an index column marked

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

  15, 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 (

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

  60).

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

• 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 for an example.

ALTER INDEX

Use the

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

Syntax

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

Description

Add an additional column into an index or remove a column from an index.

Parameters

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

• The name of the index to alter.

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

• The name of the column to add into the index or to remove from the index.

DROP INDEX

Removes a secondary index.

Syntax

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

Description

Use the

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

Parameters

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

• The name of the index to drop.

VIEW statements

CREATE VIEW and CREATE OR REPLACE VIEW

Use the

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

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

Syntax

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

Description

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

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

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

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

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

Parameters

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

• The name of the view to be created. For naming guidance, see

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

• Indicates that when the view is used in a query, the objects referenced in the view are access-checked against the credentials of the user who invoked the query. This is the only SQL security option in Google Standard SQL.

AS

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

• The query that defines the view content.

  • The query must specify a name for each item in the .

  • The query cannot include .

  • Google Standard SQL disregards any in this query that isn't paired with a .

  See Query syntax for information on constructing a query.

DROP VIEW

Removes a view.

Syntax

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

Description

Use the

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

Parameters

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

• The name of the view to drop.

ROLE statements

CREATE ROLE

Defines a new database role.

Syntax

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

Description

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

Parameters

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

• The name of the database role to create. The role name

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

  81 and role names starting with

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

  82 are reserved for system roles. See also .

Example

This example creates the database role

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

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

DROP ROLE

Drops a database role.

Syntax

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

Description

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

You can't drop a database role if it has any privileges granted to it. All privileges granted to a database role must be revoked before the role can be dropped. You can drop a database role whether or not it's granted to IAM principals.

Dropping a role automatically revokes its membership in other roles and revokes the membership of its members.

You can't drop system roles.

Parameters

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

• The name of the database role to drop.

Example

This example drops the database role

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

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

GRANT and REVOKE statements

GRANT

Syntax

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

Description

For fine-grained access control, grants privileges on one or more tables to database roles, or grants database roles to other database roles to create a database role hierarchy. When granting privileges on a table, optionally grants privileges on only a subset of table columns.

Parameters

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

• The name of an existing table.

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

• The name of an existing column in the specified table.

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

• The name of an existing database role.

Notes and restrictions

• You can grant

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

  92 only at the table level.

• When granting column-level privileges on multiple tables, each table must contain the named columns.

• If a table contains a column that is marked

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

  5 and has no default value, you can't insert into the table unless you have the

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

  94 privilege on that column.

Examples

The following example grants

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

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

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

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

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

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

The next example grants

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

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

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

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

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

The next example mixes table-level and column-level grants.

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

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

The next example makes column-level grants on two tables. Both tables must contain the

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

The next example grants

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

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

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

The next example grants the database role

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

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

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

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

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

REVOKE

Syntax

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

Description

For fine-grained access control, revokes privileges on one or more tables from database roles, or revokes database roles from other database roles. When revoking privileges on a table, optionally revokes privileges on only a subset of table columns.

Parameters

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

• The name of an existing table.

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

• The name of an existing column in the previously specified table.

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

• The name of an existing database role.

Notes and restrictions

• When revoking column-level privileges on multiple tables, each table must contain the named columns.

• A

  CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)
  

  25 statement at the column level has no effect if privileges were granted at the table level.

Examples

The following example revokes

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

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

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

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

The next example revokes

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

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

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

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

The next example shows revoking both table-level and column-level privileges in a single statement.

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

The next example revokes column-level grants on two tables. Both tables must contain the

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

The next example revokes

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

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

The following example revokes the database role

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

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

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

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

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

STATISTICS statements

ALTER STATISTICS

Changes the definition of a query optimizer statistics package.

Syntax

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

Description

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

{
  BOOL
  | INT64
  | FLOAT64
  | NUMERIC
  | STRING( length )
  | JSON
  | 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}+

• Use this clause to set an option on the specified statistics package.

Parameters

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

• The name of an existing query optimizer statistics package whose attributes are to be altered.

  To fetch existing statistics packages:

  CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)
  

  49

{
  BOOL
  | INT64
  | FLOAT64
  | NUMERIC
  | STRING( length )
  | JSON
  | 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}+

• The

  CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)
  

  51 option allows you to specify whether a given statistics package is garbage collected. A package must be set as

  CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)
  

  52 if it is used in a query hint. For more information, see .

ANALYZE

Start a new query optimizer statistics package construction.

Syntax

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

Description

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

MODEL statements

CREATE MODEL and CREATE OR REPLACE MODEL

Use the

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

Syntax

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

Description

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

As soon as the model reference is registered in a database, it can be used from queries that use the ML.Predict function.

Model registration doesn't result in copying a model from the Vertex AI to a database, but only in creation of a reference to this models' endpoint hosted in the Vertex AI. If the model's endpoint gets removed from the Vertex AI, Cloud Spanner queries referencing this model fail.

Model endpoint access control

To be able to access a registered Vertex AI model endpoint from Cloud Spanner, you need to grant access permission to Cloud Spanner's service agent account. For example, it can be done by granting the role to the

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

If the Cloud Spanner service agent account doesn't exist for your project, create it by running the following command after replacing PROJECT with the project name:

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

Parameters

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

• The name of the model to be created. See

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

• Lists of columns that define model inputs (e.g. a set of features) and outputs (for example, labels). The following types (used in the

  CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)
  

  70 field of

  CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)
  

  71) are supported:

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

  32,

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

  5,

  CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)
  

  74,

  CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)
  

  75,

  CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)
  

  9, and

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

  9 of listed types.

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

• Specifies connection settings for the remote ML serving (inference) service hosting the model.

  • endpoint is the address of the Vertex AI endpoint hosting the model provided in one of the following formats:

    • Full resource URI format:

      CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)
      

      81.

    • URL format:

      CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)
      

      82.

ALTER MODEL

Changes the definition of a model.

Syntax

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

Description

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

{
  BOOL
  | INT64
  | FLOAT64
  | NUMERIC
  | STRING( length )
  | JSON
  | 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}+

• Use this clause to set an option on the specified model.

Parameters

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

• The name of an existing model whose attributes are to be altered.

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

• Specifies connection settings for the remote ML serving (inference) service hosting the model.

  • endpoint is the address of the Vertex AI endpoint hosting the model provided in one of the following formats:

    • Full resource URI format:

      CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)
      

      81.

    • URL format:

      CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)
      

      82.

DROP MODEL

Removes a model.

Syntax

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

Description

Use the

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)

After you delete a model definition, all SQL queries referencing the deleted model fail. Dropping a model definition does not affect the underlying the Vertex AI endpoint that this model is attached to.

Which of the following claims would be the best solution to the pest problem based on the data in the graph?

Which of the following is a limitation of using only the data in the table to complete an ecological?

Which of the following methods of agricultural irrigation results in the loss of the least amount of water?

Which of the following best defines aquaculture?