# Specify an identity column

> [!WARNING]
>
> **Preview**
>
>
> This feature is
>
> subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the
> [Service Specific
> Terms](https://docs.cloud.google.com/terms/service-terms#1).
>
> Pre-GA features are available "as is" and might have limited support.
>
> For more information, see the
> [launch stage descriptions](https://cloud.google.com/products/#product-launch-stages).

> [!NOTE]
> **Note:** To provide feedback or request support for this feature, send an email to [bigquery-sql-preview-support@google.com](mailto:bigquery-sql-preview-support@google.com)

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](https://docs.cloud.google.com/bigquery/docs/primary-foreign-keys). You can also generate
primary keys by using the
[`GENERATE_UUID` function](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/utility-functions#generate_uuid)
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](https://docs.cloud.google.com/bigquery/docs/reference/rest/v2/tabledata/insertAll)
  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](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_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:

```googlesql
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](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#alter_column_set_generated_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`:

```googlesql
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:

```googlesql
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:

```googlesql
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](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#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:

```googlesql
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](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#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:

```googlesql
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`:

```bash
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](https://docs.cloud.google.com/bigquery/docs/loading-data)
into a table with an identity column by using the
[`bq load` command](https://docs.cloud.google.com/bigquery/docs/reference/bq-cli-reference#bq_load) or the
[`LOAD DATA` statement](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/load-statements).
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:

```bash
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](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#alter_column_drop_generated_statement).

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

```googlesql
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](https://docs.cloud.google.com/bigquery/docs/information-schema-columns).

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

```googlesql
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](https://docs.cloud.google.com/bigquery/docs/information-schema-tables) to
see the identity column definition in the `CREATE TABLE` DDL statement for a
table.

## What's next

- For more information about schemas, see [Specifying a schema](https://docs.cloud.google.com/bigquery/docs/schemas).
- For more information about using primary keys, see [Use primary and foreign keys](https://docs.cloud.google.com/bigquery/docs/primary-foreign-keys).
- For more information about DML statements, see [Data manipulation language statements](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/data-manipulation-language).
- For more information about loading data into BigQuery, see [Introduction to loading data](https://docs.cloud.google.com/bigquery/docs/loading-data).