Specify an identity column

This document describes how to create and use identity columns, sometimes referred to as auto-incrementing columns, which are used to create and maintain primary keys on your tables. When you insert a row into a table that has an identity column, BigQuery generates a unique integer value for that column.

Overview

An identity column is an INT64 column that is populated with unique, system-generated values.

The main use case for identity columns is to generate primary keys. You can also generate primary keys by using the GENERATE_UUID function to generate unique strings, but identity columns are generally preferred for the following reasons:

  • Integer values require less storage space than string values.
  • Using integers for table joins is more efficient than using strings.

The values for an identity column are generated based on a starting value that defines the first value, and an increment value that defines the minimum difference between successively generated values.

Generated identity column values have the following properties:

  • Unique. Automatically generated values are unique within the table.
  • Loosely ordered. Generated values aren't guaranteed to be in strictly increasing or decreasing order.
  • Sparse. Generated values aren't guaranteed to be consecutive. Some values might be skipped, but values in an identity column always differ by a multiple of the increment that you specify.

Limitations

  • A table can have at most one identity column.
  • You can read from tables with identity columns by using legacy SQL, but you can't write to tables with identity columns using legacy SQL.
  • You can't use clustering or partitioning on an identity column.

  • The following table copy operations are not supported if a source or destination table has an identity column:

    • Table copy with WRITE_APPEND or WRITE_TRUNCATE write disposition
    • Multi-source table copy

  • Streaming data by using the Storage Write API (gRPC) or the tabledata.insertAll API method is not supported for tables with identity columns.

Create identity columns

You can create an identity column when you create a new table by using the CREATE TABLE DDL statement. Use the GENERATED AS IDENTITY clause to designate an INT64 column as an identity column. A table can have at most one identity column. You can specify one of the following generation modes that determines whether you can manually insert values into the identity column:

  • GENERATED ALWAYS AS IDENTITY: values are always system-generated. You can't provide your own value when inserting or updating data in this column. If you don't specify ALWAYS or BY DEFAULT, ALWAYS is used.

  • GENERATED BY DEFAULT AS IDENTITY: you can insert or modify values in the identity column. BigQuery doesn't enforce uniqueness of values that you insert or modify.

    If you omit the column or provide NULL when you insert data, BigQuery automatically generates a value for you. The identity column can't contain a NULL value. If you want to use a generated value in an INSERT, MERGE, or UPDATE statement, you can use the DEFAULT or NULL keyword.

The following example creates the table mydataset.id_table with an identity column id that starts at 0 and increments by 5:

CREATE TABLE mydataset.id_table (
  id INT64 GENERATED ALWAYS AS IDENTITY(START WITH 0 INCREMENT BY 5),
  data STRING
);

Add the identity column property to a column

To modify an existing column to generate identity values, use the ALTER TABLE ALTER COLUMN SET GENERATED DDL statement. This statement changes an existing INT64 column into an identity column. It doesn't backfill values for existing rows in the identity column.

Use DML statements with identity columns

You can use DML statements such as INSERT, MERGE, and UPDATE with identity columns. The following sections use the table mydataset.mytable that has an identity column called id and a string column called data:

CREATE OR REPLACE TABLE mydataset.mytable (
  id INT64 GENERATED BY DEFAULT AS IDENTITY(START WITH 100 INCREMENT BY 10),
  data STRING
);

Insert data

When you insert data into a table with an identity column, you can omit the identity column from the column list to generate a value for it. The following INSERT statement omits the id column, BigQuery generates values for it:

INSERT mydataset.mytable (data) VALUES ('A'), ('B'), ('C');

The result is similar to the following, although the order of assignment of generated values to rows might vary:

+-----+------+
| id  | data |
+-----+------+
| 110 | A    |
| 120 | B    |
| 100 | C    |
+-----+------+

If an identity column is defined with GENERATED BY DEFAULT AS IDENTITY, you can specify your own value for the column. You can also use the DEFAULT keyword or NULL to have BigQuery generate a value.

The following INSERT statement provides a value for one row, and uses DEFAULT or NULL to generate values for the other two rows:

INSERT mydataset.mytable (id, data)
VALUES (155, 'D'), (DEFAULT, 'E'), (NULL, 'F');

The result is similar to the following:

+-----+------+
| id  | data |
+-----+------+
| 110 | A    |
| 120 | B    |
| 100 | C    |
| 155 | D    |
| 140 | E    |
| 130 | F    |
+-----+------+

If an identity column is defined with GENERATED ALWAYS AS IDENTITY, you can only use the DEFAULT keyword to have BigQuery generate a value. You can't provide your own value or use NULL.

Merge data

You can use the MERGE statement to merge data into a table with an identity column. If your identity column uses the GENERATED BY DEFAULT AS IDENTITY generation mode, then you can use the DEFAULT or NULL keywords to generate a value when you insert or update data as part of a MERGE statement.

The following example merges mydataset.source_table into mydataset.mytable, inserting a new row if there is no match on the data column, and updating the id column to a new generated value if there is a match:

CREATE OR REPLACE TABLE mydataset.source_table(data STRING)
AS SELECT * FROM UNNEST(['A', 'C', 'G']);

MERGE mydataset.mytable T
USING mydataset.source_table S
ON T.data = S.data
WHEN MATCHED THEN
  UPDATE SET id = DEFAULT
WHEN NOT MATCHED THEN
  INSERT(data)
  VALUES(S.data);

The result is similar to the following:

+-----+------+
| id  | data |
+-----+------+
| 160 | A    |
| 120 | B    |
| 150 | C    |
| 155 | D    |
| 140 | E    |
| 130 | F    |
| 170 | G    |
+-----+------+

If your identity column uses the GENERATED ALWAYS AS IDENTITY generation mode, then you can't include the identity column in any merge update clause. To use a merge insert clause, you can omit the identity column from the column list or use the DEFAULT keyword.

Update data

You can use the UPDATE statement to update values in an identity column that uses the GENERATED BY DEFAULT AS IDENTITY generation mode. You can use the DEFAULT or NULL keywords to generate a new value.

The following example updates all values in column id to newly generated values:

UPDATE mydataset.mytable
SET id = NULL
WHERE TRUE;

The result is similar to the following:

+-----+------+
| id  | data |
+-----+------+
| 190 | A    |
| 210 | B    |
| 240 | C    |
| 230 | D    |
| 180 | E    |
| 200 | F    |
| 220 | G    |
+-----+------+

If your identity column uses the GENERATED ALWAYS AS IDENTITY generation mode, then you can't update the identity column.

Append to a table

You can use the bq query command with the --append_table flag to append the results of a query to a destination table that has an identity column. If the query omits the identity column, a value is generated for it.

The following example appends data only for column data to mydataset.mytable:

bq query \
    --nouse_legacy_sql \
    --append_table \
    --destination_table=mydataset.mytable \
    'SELECT "H" AS data'

A new row with a generated id value is added to mydataset.mytable.

Load data

You can load data into a table with an identity column by using the bq load command or the LOAD DATA statement. If the identity column is omitted from the source data or schema, values are generated for it. If the identity column is GENERATED ALWAYS AS IDENTITY, it must be omitted.

The following example loads data from a CSV file data.csv into mydataset.mytable. The file only contains data for the data column:

"X"
"Y"

The following bq load command loads data.csv into mydataset.mytable, omitting the header row and specifying only the data column in the schema:

bq load --source_format=CSV --skip_leading_rows=0 \
mydataset.mytable data.csv data:STRING

The load job generates id values for the new rows.

Remove the identity column property

You can remove the identity property from a column by using the ALTER TABLE ALTER COLUMN DROP GENERATED DDL statement.

The following example removes identity column properties from column id in mydataset.mytable:

ALTER TABLE mydataset.mytable
ALTER COLUMN id DROP GENERATED;

View information about identity columns

To see the identity column configuration for a column, query the INFORMATION_SCHEMA.COLUMNS view.

The following example shows identity column information for columns in mydataset.mytable:

SELECT
  column_name,
  is_identity,
  identity_generation,
  identity_start,
  identity_increment
FROM
  mydataset.INFORMATION_SCHEMA.COLUMNS
WHERE
  table_name = 'mytable';

The result is similar to the following:

+-------------+-------------+---------------------+----------------+--------------------+
| column_name | is_identity | identity_generation | identity_start | identity_increment |
+-------------+-------------+---------------------+----------------+--------------------+
| id          | YES         | BY DEFAULT          | 100            | 10                 |
| data        | NO          | NULL                | NULL           | NULL               |
+-------------+-------------+---------------------+----------------+--------------------+

Alternatively, you can query the ddl column of the INFORMATION_SCHEMA.TABLES view to see the identity column definition in the CREATE TABLE DDL statement for a table.

What's next