Data definition language

Data definition language (DDL) statements let you create, alter, and delete ZetaSQL resources.

`CREATE DATABASE`

CREATE
  DATABASE
  database_name
  [OPTIONS (key=value, ...)]

Description

The CREATE DATABASE statement creates a database. If you have schema options, you can add them when you create the database. These options are system-specific and follow the ZetaSQL HINT syntax.

Example

CREATE DATABASE library OPTIONS(
  base_dir=`/city/orgs`,
  owner='libadmin'
);

/*--------------------*
 | Database           |
 +--------------------+
 | library            |
 *--------------------*/

`CREATE TABLE`

Note: Some documentation is pending for this feature.

CREATE
   [ OR REPLACE ]
   [ TEMP[ORARY] ]
   TABLE
   [ IF NOT EXISTS ]
   table_name [ ( table_element, ... ) ]
   [ PARTITION [ hints ] BY partition_expression, ... ]
   [ CLUSTER [ hints ] BY cluster_expression, ... ]
   [ OPTIONS (key=value, ...) ]
   [ AS query ];

table_element:
   column_definition | constraint_definition

Description

The CREATE TABLE statement creates a table and adds any table elements defined in the table element list (table_element, ...). If the AS query clause is absent, the table element list must be present and contain at least one column definition. A table element can be a column definition or constraint definition. To learn more about column definitions, see Defining Columns. To learn more about constraint definitions, see Defining Constraints.

In a table, if both the table element list and the AS query clause are present, then the number of columns in the table element list must match the number of columns selected in query, and the type of each column selected in query must be assignable to the column type in the corresponding position of the table element list.

A column is assignable if:

It's data type can be coerced into the corresponding table element column type.
If the column is an INT64 type and the corresponding table element is an INT32 type.
If the column is an UINT64 type and the corresponding table element is an UINT32 type.

Optional Clauses

OR REPLACE: Replaces any table with the same name if it exists. Cannot appear with IF NOT EXISTS.
TEMP | TEMPORARY: Creates a temporary table. The lifetime of the table is system-specific.
IF NOT EXISTS: If any table exists with the same name, the CREATE statement will have no effect. Cannot appear with OR REPLACE.
PARTITION BY: Creates partitions of a table. The expression cannot contain floating point types, non-groupable types, constants, aggregate functions, or window functions.
CLUSTER BY: Co-locates rows if the rows are not distinct for the values produced by the provided list of expressions. If the table was partitioned, co-location occurs within each partition. If the table was not partitioned, co-location occurs within the table.
OPTIONS: If you have schema options, you can add them when you create the table. These options are system-specific and follow the ZetaSQLHINT syntax
AS query: Materializes the result of query into the new table.

Examples

Create a table.

CREATE TABLE books (title STRING, author STRING);

Create a table in a schema called library.

CREATE TABLE library.books (title STRING, author STRING);

Create a table that contains schema options.

CREATE TABLE books (title STRING, author STRING) OPTIONS (storage_kind=FLASH_MEMORY);

Partition a table.

CREATE TABLE books (title STRING, author STRING, publisher STRING, release_date DATE)
PARTITION BY publisher, author;

Partition a table with a pseudocolumn. In the example below, SYSDATE represents the date when the book was added to the database. Replace SYSDATE with a psuedocolumn supported by your SQL service.

CREATE TABLE books (title STRING, author STRING)
PARTITION BY sysdate;

Cluster a table.

CREATE TABLE books (
  title STRING,
  first_name STRING,
  last_name STRING
)
CLUSTER BY last_name, first_name;

Defining columns

column_definition:
  column_name
  {
    column_type
    | [ column_type ] generated_column_clause
  }
  [ column_attribute[ ... ] ]
  [ OPTIONS (...) ]

column_attribute:
  PRIMARY KEY
  | NOT NULL
  | HIDDEN
  | [ CONSTRAINT constraint_name ] foreign_reference

Description

A column exists within a table. Each column describes one attribute of the rows that belong to the table.

Columns in a table are ordered. The order will have consequences on some sorts of SQL statements such as SELECT * FROM Table and INSERT INTO Table VALUES (...).

Definitions

column_name: The name of the column. The name of a column must be unique within a table.
column_type: The ZetaSQL data type of the column.
generated_column_clause: A generated column. To learn more, see Defining the generated column clause.
column_attribute: A characteristic of the column. This can be:
- PRIMARY KEY: Defines this column as the primary key of the table.
- NOT NULL: A value on a column cannot be null. More specifically, this is shorthand for a constraint of the shape CHECK [column_name] IS NOT NULL.
- HIDDEN: Hides a column if it should not appear in SELECT * expansions or in structifications of a row variable, such as SELECT t FROM Table t.
- [ CONSTRAINT constraint_name ] foreign_reference: The column in a foreign table to reference. You can optionally name the constraint. A constraint name must be unique within its schema; it cannot share the name of other constraints in the table. If a constraint name is not provided, one is generated by the implementing engine. Users can use INFORMATION_SCHEMA to look up the generated names of table constraints.
OPTIONS: If you have schema options, you can add them when you create the column. These options are system-specific and follow the ZetaSQLHINT syntax

Examples

Create a table with a primary key that can't be NULL.

CREATE TABLE books (title STRING, author STRING, isbn INT64 PRIMARY KEY NOT NULL);

Create a table with a generated column. In this example, the generated column holds the first and last name of an author.

CREATE TABLE Authors (
  first_name STRING HIDDEN,
  last_name STRING HIDDEN,
  full_name STRING GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)));

Create a table that contains schema options on column definitions.

CREATE TABLE books (
  title STRING NOT NULL PRIMARY KEY,
  author STRING
      OPTIONS (is_deprecated=true, comment="Replaced by authorId column"),
  authorId INT64 REFERENCES authors (id),
  category STRING OPTIONS (description="LCC Subclass")
)

Defining table constraints

constraint_definition:
   primary_key
   | foreign_key
   | check_constraint

primary_key:
  PRIMARY KEY (column_name [ ASC | DESC ], ... )
  [ OPTIONS (...) ]

foreign_key:
  [ CONSTRAINT constraint_name ]
  FOREIGN KEY (column_name, ... )
  foreign_reference
  [ OPTIONS (...) ]

check_constraint:
  [ CONSTRAINT constraint_name ]
  CHECK ( boolean_expression )
  [ ENFORCED | NOT ENFORCED ]
  [ OPTIONS (...) ]

Description

A constraint_definition is a rule enforced on the columns of a table.

Constraint Kinds

primary_key: Defines the primary key for a table.
foreign_key: Defines a foreign key for a table. A foreign key links two tables together.
check_constraint: Restricts the data that can be added to certain columns used by the expressions of the constraints.

Optional Clauses

CONSTRAINT: Names the constraint. A constraint name must be unique within its schema; it cannot share the name of other constraints. If a constraint name is not provided, one is generated by the implementing engine. Users can use INFORMATION_SCHEMA to look up the generated names of table constraints.
ASC | DESC: Specifies that the engine should optimize reading the index records in ascending or descending order by this key part. ASC is the default. Each key part will be sorted with respect to the sort defined by any key parts to the left.
ENFORCED | NOT ENFORCED: Specifies whether or not the constraint is enforced.
OPTIONS: If you have schema options, you can add them when you create the constraint. These options are system-specific and follow the ZetaSQLHINT syntax

Examples

Create a primary key constraint, using the title and author columns in a table called books.

CREATE TABLE books (title STRING, author STRING, PRIMARY KEY (title ASC, author ASC));

Create a foreign key constraint. When data in the top_authors table is updated or deleted, make the same change in the authors table.

CREATE TABLE top_authors (
  author_first_name STRING,
  author_last_name STRING,
  CONSTRAINT fk_top_authors_name
    FOREIGN KEY (author_first_name, author_last_name)
    REFERENCES authors (first_name, last_name)
);

Create a check constraint. A row that contains values for words_per_chapter and words_per_book can only only be inserted into the page_count_average table if the words_per_chapter value is less than the words_per_book value.

CREATE TABLE page_count_average (
  words_per_chapter INT64,
  words_per_book INT64,
  CHECK (words_per_chapter < words_per_book)
);

Defining foreign references

foreign_reference:
  REFERENCES table_name (column_name, ... )
  [ MATCH { SIMPLE | FULL | NOT DISTINCT } ]
  [ ON UPDATE referential_action ]
  [ ON DELETE referential_action ]
  [ ENFORCED | NOT ENFORCED ]

referential_action:
  NO ACTION | RESTRICT | CASCADE | SET NULL

Description

A foreign key is used to define a relationship between the rows in two tables. The foreign key in one table can reference one or more columns in another table. A foreign reference can be used to assign constraints to a foreign key and to give a foreign key a unique name.

Optional Clauses

MATCH: Specifies when a constraint validation for a referencing row passes or fails. Your choices are:
- SIMPLE:
  - Passes if any column of the local key is NULL.
  - Passes if the columns of the local key are pairwise-equal to the columns of the referenced key for some row of the referenced table.
- FULL:
  - Passes if all columns of the local key are NULL.
  - Passes if the columns of the local key are pairwise-equal to the columns of the referenced key for some row of the referenced table.
- NOT DISTINCT:
  - Passes if the columns of the local key are pairwise-not-distinct from the columns of the referenced key for some row of the referenced table.
ON UPDATE: If data in the referenced table updates, your choices are:
- RESTRICT: Fail the transaction.
- NO ACTION: Don't update the referencing table. If the data in the referencing table does not satisfy the constraint before it is checked, the transaction will fail.
- CASCADE: For each updated row in the referenced table, update all matching rows in the referencing table so that they continue to match the same row after the transaction.
- SET NULL: Any change to a referenced column in the referenced table causes the corresponding referencing column in matching rows of the referencing table to be set to null.
ON DELETE: If data in the referenced table is deleted, your choices are:
- RESTRICT: Fail the transaction.
- NO ACTION: Don't delete the data in the referencing table. If the data in the referencing table does not satisfy the constraint before it is checked, the transaction will fail.
- CASCADE: For each deleted row in the referenced table, delete all matching rows in the referencing table so that they continue to match the same row after the transaction.
- SET NULL: If a row of the referenced table is deleted, then all referencing columns in all matching rows of the referencing table are set to null.
ENFORCED | NOT ENFORCED: Specifies whether or not the constraint is enforced.

Examples

When data in the top_books table is updated or deleted, make the same change in the books table.

CREATE TABLE top_books (
  book_name STRING,
  CONSTRAINT fk_top_books_name
    FOREIGN KEY (book_name)
    REFERENCES books (title)
    ON UPDATE CASCADE
    ON DELETE CASCADE
);

Defining the generated column clause

generated_column_clause:
  [ GENERATED [ ALWAYS ] ] AS
  ( generated_column_expression )
  [ STORED ]

Description

A generated column is a column in a base table whose value is defined as a function of other columns in the same row.

Definitions

column_type: The ZetaSQL data type of the column.
generated_column_expression: A scalar expression that produces the content for the generated column. Subqueries are not allowed. This expression can:
- Reference any non-generated column of the containing table, regardless of column order.
- Reference other generated columns, but recursion (or transitive recursion) is not permitted.
- Call built-in functions.
- Call UDFs that do not access SQL data.
STORED: Controls whether the column is pre-computed and persisted in storage when the row is inserted or updated.

When this keyword is not present, the column is recomputed in every query that reads it. That means that the expression can be volatile and no storage is consumed. So if you use the RAND() function, then a different random number is generated each time the column is read.

When this keyword is present, the column value is precomputed on update so that the value does not have to be recomputed each time it is read. That consumes storage space. This also means that volatile expressions won't work as intended. For example, the function RAND() will generate a random number once, and that first number will be used each time the column is read.

Details

By default, a generated column is not stored and the column is computed when the row is read.

A generated column can appear as an index key, primary key, or foreign key if it satisfies the criteria for storage space.

If column_type and generated_column_clause are both defined, then the type of the expression in generated_column_clause must be assignable to column_type. For more information about assignable types, see the assignable types note for columns in the CREATE TABLE statement.

When a generated column is specified in a CREATE TABLE AS SELECT statement, the query must not have an output column for the generated column.

If computation of a non-stored generated column expression experiences a runtime error when reading a query, the query fails. If a stored generated column expression experiences a runtime error when being written, the write operation fails.

Example

This query creates a table with a generated column. The generated column holds the first and last name of an author.

CREATE TABLE Authors (
  first_name STRING HIDDEN,
  last_name STRING HIDDEN,
  full_name STRING GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name));
);

Using `CREATE TABLE AS`

CREATE TABLE table_definition AS query

Description

The CREATE TABLE AS statement creates a table and materializes the result of a query into this new table. To learn more about the syntax for this statement, see CREATE TABLE.

Examples

Copy all rows from a table called old_books to a new table called books.

CREATE TABLE books AS (SELECT * FROM old_books);

Copy rows from a table called old_books to a new table called books. If a book was published before 1900, do not add it to books.

CREATE TABLE books
AS (SELECT * FROM old_books where year >= 1900)

Copy rows from a table called old_books to a new table called books. Only copy the title column into books.

CREATE TABLE books AS (SELECT title FROM old_books);

Copy rows from old_books and ancient_books to a new table called books.

CREATE TABLE books AS (
  SELECT * FROM old_books UNION ALL
  SELECT * FROM ancient_books);

`CREATE VIEW`

Note: The RECURSIVE keyword has been added, but documentation is pending.

CREATE
  [OR REPLACE]
  [TEMP[ORARY]]
  VIEW
  [IF NOT EXISTS]
  view_name
  [SQL SECURITY { INVOKER | DEFINER }]
  [OPTIONS (key=value, ...)]
AS query;

Description

The CREATE VIEW statement creates a view based on a specific query.

Optional Clauses

OR REPLACE: Replaces any view with the same name if it exists. Cannot appear with IF NOT EXISTS.
TEMP | TEMPORARY: Creates a temporary view. The lifetime of the table is system-specific.
IF NOT EXISTS: If any view exists with the same name, the CREATE statement will have no effect. Cannot appear with OR REPLACE.
SQL SECURITY: Specifies how data access control lists (ACLs) are applied with respect to schema objects referenced within the view's query.
- DEFINER: The privileges of the role (user) that created the view are used to run the view, and are applied to each schema object the view accesses.
- INVOKER: The privileges of the role (user) that is running the query that invoked the view are used to run the view, and are applied to each schema object the view accesses.

`CREATE EXTERNAL TABLE`

CREATE
  [OR REPLACE]
  [TEMP[ORARY]]
  EXTERNAL TABLE
  [IF NOT EXISTS]
  table_name
  [OPTIONS (key=value, ...)];

Description

The CREATE EXTERNAL TABLE creates a table from external data. CREATE EXTERNAL TABLE also supports creating persistent definitions.

The CREATE EXTERNAL TABLE does not build a new table; instead, it creates a pointer to data that exists outside of the database.

Optional Clauses

OR REPLACE: Replaces any table with the same name if it exists. Cannot appear with IF NOT EXISTS.
TEMP | TEMPORARY: Creates a temporary table. The lifetime of the table is system-specific.
IF NOT EXISTS: If any table exists with the same name, the CREATE statement will have no effect. Cannot appear with OR REPLACE.

`CREATE SNAPSHOT TABLE`

Documentation is pending for this feature.

`CREATE INDEX`

CREATE
  [OR REPLACE]
  [UNIQUE]
  INDEX
  [IF NOT EXISTS]
  index_name
  ON
  table_name [[AS] alias]
  [UNNEST(array_expression) [[AS] alias] [WITH OFFSET [[AS] alias]] ...]
  (key_expression [ASC|DESC], ...)
  [STORING (stored_expression, ...)]
  [OPTIONS (key=value, ...)];

Description

The CREATE INDEX statement creates a secondary index for one or more values computed from expressions in a table.

Expressions

array_expression: An immutable expression that is used to produce an array value from each row of an indexed table.
key_expression: An immutable expression that is used to produce an index key value. The expression must have a type that satisfies the requirement of an index key column.
stored_expression: An immutable expression that is used to produce a value stored in the index.

Optional Clauses

OR REPLACE: If the index already exists, replace it. This cannot appear with IF NOT EXISTS.
UNIQUE: Do not index the same key multiple times. Systems can choose how to resolve conflicts in case the index is over a non-unique key.
IF NOT EXISTS: Do not create an index if it already exists. This cannot appear with OR REPLACE.
UNNEST(array_name): Create an index for the elements in an array.
WITH OFFSET: Return a separate column containing the offset value for each row produced by the UNNEST operation.
ASC | DESC: Sort the indexed values in ascending or descending order. ASC is the default value with respect to the sort defined by any key parts to the left.
STORING: Specify additional values computed from the indexed base table row to materialize with the index entry.
OPTIONS: If you have schema options, you can add them when you create the index. These options are system-specific and follow the ZetaSQLHINT syntax.

Examples

Create an index on a column in a table.

CREATE INDEX i1 ON KeyValue (Key);

Create an index on multiple columns in a table.

CREATE INDEX i1 ON KeyValue (Key, Value);

If the index already exists, replace it.

CREATE OR REPLACE INDEX i1 ON KeyValue (Key, Value);

If the index already exists, don't replace it.

CREATE INDEX IF NOT EXISTS i1 ON KeyValue (Key, Value);

Create an index that contains unique values.

CREATE UNIQUE INDEX i1 ON Books (Title);

Create an index that contains a schema option.

CREATE INDEX i1 ON KeyValue (Value) OPTIONS (page_count=1);

Reference the table name for a column.

CREATE INDEX i1 ON KeyValue (KeyValue.Key, KeyValue.Value);

CREATE INDEX i1 on KeyValue AS foo (foo.Key, foo.Value);

Use the path expression for a key.

CREATE INDEX i1 ON KeyValue (Key.sub_field1.sub_field2);

Choose the sort order for the columns assigned to an index.

CREATE INDEX i1 ON KeyValue (Key DESC, Value ASC);

Create an index on an array, but not the elements in an array.

CREATE INDEX i1 ON Books (BookList);

Create an index for the elements in an array.

CREATE INDEX i1 ON Books UNNEST (BookList) (BookList);

CREATE INDEX i1 ON Books UNNEST (BookListA) AS a UNNEST (BookListB) AS b (a, b);

Create an index for the elements in an array using an offset.

CREATE index i1 on Books UNNEST(BookList) WITH OFFSET (BookList, offset);

CREATE index i1 on Books UNNEST(BookList) WITH OFFSET AS foo (BookList, foo);

Store an additional column but don't sort it.

CREATE INDEX i1 ON KeyValue (Value) STORING (Key);

Store multiple additional columns and don't sort them.

CREATE INDEX i1 ON Books (Title) STORING (First_Name, Last_Name);

Store a column but don't sort it. Reference a table name.

CREATE INDEX i1 ON Books (InStock) STORING (Books.Title);

Use an expression in the STORING clause.

CREATE INDEX i1 ON KeyValue (Key) STORING (Key+1);

Use an implicit alias in the STORING clause.

CREATE INDEX i1 ON KeyValue (Key) STORING (KeyValue);

`CREATE_SCHEMA`

Documentation is pending for this feature.

`CREATE CONSTANT`

CREATE
  [OR REPLACE]
  [{ TEMP[ORARY] | PUBLIC | PRIVATE }]
  CONSTANT
  [IF NOT EXISTS]
  constant_name = constant_value;

Description

The CREATE CONSTANT statement creates a constant and assigns a value to it. The value cannot be altered later with the ALTER statement, but you can use CREATE OR REPLACE CONSTANT if you need to replace the value.

OR REPLACE: Replace a constant if it already exists. This cannot appear with IF NOT EXISTS.
TEMP | TEMPORARY: Creates a temporary constant. The lifetime of the constant is system-specific.
PUBLIC: If the constant is declared in a module, PUBLIC specifies that it is available outside of the module.
PRIVATE: If the constant is declared in a module, PRIVATE specifies that it is only available inside of the module (default).
IF NOT EXISTS: Do not create a constant if it already exists. If the constant already exists, don't produce an error and keep the existing value. This cannot appear with OR REPLACE.
constant_name: The name of the constant. This can include a path.
constant_value: An expression that represents the value for the constant.

The constant declaration does not specify a type. The constant type is the constant_value type. You cannot ALTER, RENAME, or DROP a constant.

Constants cannot be used as arguments to aggregate user-defined functions (UDFs).

Example

Create a constant, DEFAULT_HEIGHT:

CREATE TEMPORARY CONSTANT DEFAULT_HEIGHT = 25;

Use it in a statement:

SELECT (DEFAULT_HEIGHT + 5) AS result;

/*--------*
 | result |
 +--------+
 | 30     |
 *--------*/

`CREATE AGGREGATE FUNCTION`

A user-defined aggregate function (UDA), lets you create an aggregate function using another SQL expression or another programming language. These functions accept arguments and perform actions, returning the result of those actions as a value. To create a UDA, see UDAs.

`CREATE FUNCTION`

A user-defined function (UDF), lets you create a scalar function using another SQL expression or another programming language. These functions accept arguments and perform actions, returning the result of those actions as a value. To create a UDF, see UDFs.

`CREATE MODEL`

Documentation is pending for this feature.

`CREATE PLACEMENT`

Documentation is pending for this feature.

`CREATE PROCEDURE`

Documentation is pending for this feature.

`CREATE ROW POLICY`

Documentation is pending for this feature.

`CREATE PRIVILEGE RESTRICTION`

CREATE
  [ OR REPLACE ]
  PRIVILEGE RESTRICTION
  [ IF NOT EXISTS ]
  ON SELECT (column_list)
  ON object_type object_path
  [ RESTRICT TO (exemptee_list) ];

column_list:
  column_name[, ...]

object_type:
  { TABLE | VIEW }

exemptee_list:
  { mdb_user | gaia_user | mdb_group }[, ...]

Description

The CREATE PRIVILEGE RESTRICTION statement restricts who can select a list of columns from a table or view. Users and groups not in the exemptee list are denied from exercising these privileges on the object.

Warning: Creating a privilege restriction can break downstream dependencies. For tips to avoid this, see [Privileges and Restrictions] ((broken link)).

Parameters

column_list: The list of columns on which to add privilege restrictions.
object_type: The type of object on which to add privilege restrictions. Restrictions can be added for tables and views.
object_path: The path of the object.
exemptee_list: Comma-delimited list of quoted users and groups. Only users and groups with READER privileges on the object who are also in this list can exercise those privileges.

Optional Clauses

OR REPLACE: This creates restrictions if they don't exist or overwrites them if they do. Can't be used with IF NOT EXISTS.
IF NOT EXISTS: This creates restrictions if they don't exist or does nothing if they do. Can't be used with OR REPLACE.
If neither OR REPLACE nor IF NOT EXISTS is specified, this creates restrictions if they don't exist or returns an error if they do.
RESTRICT TO: If specified, sets the ACL of the privilege restriction to exemptee_list. If not specified, the privilege restriction is interpreted as having an empty ACL, in which case no users can exercise the privileges.

`CREATE PROPERTY GRAPH`

Creates a property graph. For more information, see CREATE PROPERTY GRAPH in the GQL reference.

`CREATE TABLE FUNCTION`

A table function, also known as a table-valued function (TVF), returns a table. A TVF is called in the FROM clause like a table subquery. To create a TVF, see TVFs.

`DEFINE TABLE`

DEFINE TABLE table_name (options);

Description

The DEFINE TABLE statement allows queries to run against an exported data source.

`ALTER`

Note: Some documentation is pending for this feature.

ALTER object_type alter_action, ...

object_type:
  TABLE
  | VIEW

alter_action:
  SET OPTIONS (key=value, ...)
  | ADD COLUMN [ IF NOT EXISTS ]  column_definition
  | DROP COLUMN [ IF EXISTS ]  column_name

Description

The ALTER statement modifies metadata for a table or a view.

table_name is any identifier or dotted path.

The option entries are system-specific. These follow the ZetaSQL HINT syntax.

SET OPTIONS action raises an error under these conditions:

The table does not exist.
A key is not recognized.

The following semantics apply:

The value is updated for each key in the SET OPTIONS clause.
Keys not in the SET OPTIONS clause remain unchanged.
Setting a key value to NULL clears it.

ADD and DROP COLUMN actions raise an error under these conditions:

ADD COLUMN or DROP COLUMN are applied to a view.
ADD COLUMN specified without IF NOT EXIST and the column already exists.
DROP COLUMN specified without IF EXIST and the column does not exist.

Examples

The following examples illustrate ways to use the ALTER SET OPTIONS statement:

Update table description.

ALTER TABLE my_dataset.my_table
SET OPTIONS (description='my table');

Remove table description.

ALTER TABLE my_dataset.my_table
SET OPTIONS (description=NULL);

The following example illustrates using the ALTER ADD COLUMN statement:

ALTER TABLE mydataset.mytable
    ADD COLUMN A STRING,
    ADD COLUMN IF NOT EXISTS B GEOGRAPHY,
    ADD COLUMN C ARRAY<NUMERIC>,
    ADD COLUMN D DATE OPTIONS(description="my description")

Add column A of type STRUCT.

ALTER TABLE mydataset.mytable
ADD COLUMN A STRUCT<
               B GEOGRAPHY,
               C ARRAY<INT64>,
               D INT64 NOT NULL,
               E TIMESTAMP OPTIONS(description="creation time")
             >

`RENAME`

RENAME object_type old_name_path TO new_name_path;

Description

The RENAME object renames an object. object_type indicates what type of object to rename.

`DROP`

Note: Some documentation is pending for this feature.

DROP object_type [IF EXISTS] object_path;

Description

The DROP statement drops an object. object_type indicates what type of object to drop.

Optional Clauses

IF EXISTS: If no object exists at object_path, the DROP statement will have no effect.

`DROP PROPERTY GRAPH`

Deletes a property graph. For more information, see DROP PROPERTY GRAPH in the GQL reference.

Terminology

Primary key

A primary key constraint is an attribute of a table. A table can have at most one primary key constraint that includes one or more columns of that table. Each row in the table has a tuple that is the row's primary key. The primary key tuple has values that correspond to the columns in the primary key constraint. The primary key of all rows must be unique among the primary keys of all rows in the table.

Examples

In this example, a column in a table called books is assigned to a primary key. For each row in this table, the value in the id column must be distinct from the value in the id column of all other rows in the table.

CREATE TABLE books (title STRING, id STRING, PRIMARY KEY (id));

In this example, multiple columns in a table called books are assigned to a primary key. For each row in this table, the tuple of values in the title and name columns must together be distinct from the values in the respective title and name columns of all other rows in the table.

CREATE TABLE books (title STRING, name STRING, PRIMARY KEY (title, name));

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

data-definition-language.md

data-definition-language.md

Data definition language

`CREATE DATABASE`

`CREATE TABLE`

Defining columns

Defining table constraints

Defining foreign references

Defining the generated column clause

Using `CREATE TABLE AS`

`CREATE VIEW`

`CREATE EXTERNAL TABLE`

`CREATE SNAPSHOT TABLE`

`CREATE INDEX`

`CREATE_SCHEMA`

`CREATE CONSTANT`

`CREATE AGGREGATE FUNCTION`

`CREATE FUNCTION`

`CREATE MODEL`

`CREATE PLACEMENT`

`CREATE PROCEDURE`

`CREATE ROW POLICY`

`CREATE PRIVILEGE RESTRICTION`

`CREATE PROPERTY GRAPH`

`CREATE TABLE FUNCTION`

`DEFINE TABLE`

`ALTER`

`RENAME`

`DROP`

`DROP PROPERTY GRAPH`

Terminology

Primary key

Files

data-definition-language.md

Latest commit

History

data-definition-language.md

File metadata and controls

Data definition language

CREATE DATABASE

CREATE TABLE

Defining columns

Defining table constraints

Defining foreign references

Defining the generated column clause

Using CREATE TABLE AS

CREATE VIEW

CREATE EXTERNAL TABLE

CREATE SNAPSHOT TABLE

CREATE INDEX

CREATE_SCHEMA

CREATE CONSTANT

CREATE AGGREGATE FUNCTION

CREATE FUNCTION

CREATE MODEL

CREATE PLACEMENT

CREATE PROCEDURE

CREATE ROW POLICY

CREATE PRIVILEGE RESTRICTION

CREATE PROPERTY GRAPH

CREATE TABLE FUNCTION

DEFINE TABLE

ALTER

RENAME

DROP

DROP PROPERTY GRAPH

Terminology

Primary key

`CREATE DATABASE`

`CREATE TABLE`

Using `CREATE TABLE AS`

`CREATE VIEW`

`CREATE EXTERNAL TABLE`

`CREATE SNAPSHOT TABLE`

`CREATE INDEX`

`CREATE_SCHEMA`

`CREATE CONSTANT`

`CREATE AGGREGATE FUNCTION`

`CREATE FUNCTION`

`CREATE MODEL`

`CREATE PLACEMENT`

`CREATE PROCEDURE`

`CREATE ROW POLICY`

`CREATE PRIVILEGE RESTRICTION`

`CREATE PROPERTY GRAPH`

`CREATE TABLE FUNCTION`

`DEFINE TABLE`

`ALTER`

`RENAME`

`DROP`

`DROP PROPERTY GRAPH`