# Default values and identity columns in BigQuery

> How DEFAULT works in BigQuery tables (what you can use, when it applies, what it skips) and how identity columns generate unique integers, including the ALWAYS and BY DEFAULT modes and their limits.

Source: https://docs.querylab.io/bigquery/default-values-and-identity-columns/

Updated: 2026-10-06

A column `DEFAULT` fills in a value when an insert leaves the column out. An identity column is an `INT64` column where BigQuery generates a unique number for you. Both apply only to new rows. An explicit `NULL` is not replaced by a default, and an identity column does not make anything sequential.

The examples run on temporary tables in a script. The syntax is the same on stored tables.

## Column defaults

```sql
CREATE TABLE sales.orders (
  order_id INT64,
  status STRING DEFAULT 'new',
  quantity INT64 DEFAULT 1,
  note STRING
);
```

A column with no `DEFAULT` has the default `NULL`. The default is used when the insert omits the column, or when the statement writes the keyword `DEFAULT` in the column's place. Four inserts, four behaviors:

**omitted, DEFAULT, NULL and explicit values**

```sql
CREATE TEMP TABLE orders (
  order_id INT64,
  status STRING DEFAULT 'new',
  quantity INT64 DEFAULT 1,
  note STRING
);

INSERT INTO orders (order_id) VALUES (1);
INSERT INTO orders (order_id, status, quantity) VALUES (2, DEFAULT, DEFAULT);
INSERT INTO orders (order_id, status, quantity) VALUES (3, NULL, NULL);
INSERT INTO orders VALUES (4, 'paid', 5, 'gift');

SELECT * FROM orders ORDER BY order_id
```

Result in BigQuery (4 rows):

| order_id | status | quantity | note |
| --- | --- | --- | --- |
| 1 | new | 1 | NULL |
| 2 | new | 1 | NULL |
| 3 | NULL | NULL | NULL |
| 4 | paid | 5 | gift |

Row 3 is the one that catches people: `NULL` is a value, so the default does not apply. Write `DEFAULT`, or leave the column out.

### What a default can be

A default is a literal, or a call to one of these functions: `CURRENT_DATE`, `CURRENT_DATETIME`, `CURRENT_TIME`, `CURRENT_TIMESTAMP`, `GENERATE_UUID`, `RAND`, `SESSION_USER` and `ST_GEOGPOINT`. You can build a `STRUCT` or `ARRAY` from them, and a struct default has to be set for the whole struct. Anything else is refused:

**UPPER() in a default**

```sql
CREATE TEMP TABLE customers (
  customer_id INT64,
  tier STRING DEFAULT UPPER('standard')
);
SELECT 1
```

BigQuery rejects this query:

```text
Invalid value: Invalid default value expression for column tier; Found unsupported function call 'UPPER' at [1:1]
```

A default can't use another column, and it can't contain a subquery:

**a default that reads a column**

```sql
CREATE TEMP TABLE orders (
  quantity INT64,
  quantity_plus_one INT64 DEFAULT quantity + 1
);
SELECT 1
```

BigQuery rejects this query:

```text
Query error: Default value expressions cannot reference columns at [3:35]
```

The type of the expression has to fit the column. `DEFAULT 'abc'` on an `INT64` column fails with `Column default expression has type STRING which cannot be assigned to column type INT64`.

Querylab.io checks these rules in the editor, so a default that calls a function BigQuery does not allow, reads another column, or has the wrong type is underlined before you run the `CREATE TABLE` ([Code quality](https://docs.querylab.io/writing-sql/code-quality/)).

Functions run as the row is written, once per row. A `GENERATE_UUID()` default gives each inserted row its own value:

**3 rows, 3 different tokens**

```sql
CREATE TEMP TABLE events (
  event_id INT64,
  token STRING DEFAULT GENERATE_UUID()
);
INSERT INTO events (event_id) VALUES (1), (2), (3);

SELECT COUNT(*) AS events, COUNT(DISTINCT token) AS distinct_tokens
FROM events
```

Result in BigQuery (1 row):

| events | distinct_tokens |
| --- | --- |
| 3 | 3 |

### Changing a default

```sql
ALTER TABLE sales.customers ALTER COLUMN tier SET DEFAULT 'standard';
ALTER TABLE sales.customers ALTER COLUMN tier DROP DEFAULT;
```

A change affects future inserts only. Existing rows keep what they have:

**row 1 stays 'basic', row 2 gets 'standard'**

```sql
CREATE TEMP TABLE customers (customer_id INT64, tier STRING);
INSERT INTO customers VALUES (1, 'basic');

ALTER TABLE customers ALTER COLUMN tier SET DEFAULT 'standard';
INSERT INTO customers (customer_id) VALUES (2);

SELECT customer_id, tier FROM customers ORDER BY customer_id
```

Result in BigQuery (2 rows):

| customer_id | tier |
| --- | --- |
| 1 | basic |
| 2 | standard |

### Adding a column with a default

`ADD COLUMN ... DEFAULT` is refused, and the error says how to do it in three steps:

**ADD COLUMN tier STRING DEFAULT 'standard'**

```sql
CREATE TEMP TABLE customers (customer_id INT64);
INSERT INTO customers VALUES (1);

ALTER TABLE customers ADD COLUMN tier STRING DEFAULT 'standard'
```

BigQuery rejects this query:

```text
Query error: Add field with default value to an existing table schema is not supported; Field: tier. You can achieve the same result by executing the following 3 statements:
1. ALTER TABLE my_table ADD COLUMN tier;
2. ALTER TABLE my_table ALTER COLUMN tier SET DEFAULT 'standard';
3. UPDATE my_table SET tier = 'standard' WHERE TRUE;
 at [4:1]
```

The three steps, with the `UPDATE` that fills the existing rows, because the default alone leaves them `NULL`:

**add the column, set the default, backfill**

```sql
CREATE TEMP TABLE customers (customer_id INT64);
INSERT INTO customers VALUES (1);

ALTER TABLE customers ADD COLUMN tier STRING;
ALTER TABLE customers ALTER COLUMN tier SET DEFAULT 'standard';
UPDATE customers SET tier = 'standard' WHERE tier IS NULL;
INSERT INTO customers (customer_id) VALUES (2);

SELECT customer_id, tier FROM customers ORDER BY customer_id
```

Result in BigQuery (2 rows):

| customer_id | tier |
| --- | --- |
| 1 | standard |
| 2 | standard |

### Copies don't keep defaults

A table made with `CREATE TABLE ... AS SELECT` has the same columns but no defaults:

**the copy has NULL where the original has 'new'**

```sql
CREATE TEMP TABLE orders (order_id INT64, status STRING DEFAULT 'new');
CREATE TEMP TABLE orders_copy AS SELECT * FROM orders;

INSERT INTO orders (order_id) VALUES (1);
INSERT INTO orders_copy (order_id) VALUES (1);

SELECT 'original' AS source, order_id, status FROM orders
UNION ALL
SELECT 'copy', order_id, status FROM orders_copy
ORDER BY source
```

Result in BigQuery (2 rows):

| source | order_id | status |
| --- | --- | --- |
| copy | 1 | NULL |
| original | 1 | new |

If you need the defaults, create the table with its full definition and then insert into it.

### Defaults with loads and other statements

`MERGE` and `UPDATE` can use `DEFAULT` too, as in `UPDATE t SET b = DEFAULT WHERE TRUE`. A load job applies defaults when the loaded data has fewer columns than the table, but a `NULL` in the file stays `NULL`. In `INFORMATION_SCHEMA.COLUMNS` the `column_default` column shows the expression, for example `'x'` for a string default.

## Identity columns

An identity column is an `INT64` column that BigQuery fills with a unique number. It is the BigQuery answer to auto-increment, and Google suggests it instead of `GENERATE_UUID()` for surrogate keys because integers take less space and join faster.

```sql
CREATE TABLE sales.tickets (
  ticket_id INT64 GENERATED ALWAYS AS IDENTITY(START WITH 100 INCREMENT BY 10),
  title STRING
);
```

`START WITH` is the first value and `INCREMENT BY` the step. The parentheses are required: `GENERATED ALWAYS AS IDENTITY` on its own is a syntax error, and `IDENTITY()` takes the defaults. An increment of 0 is refused (`INCREMENT BY cannot be 0`), and a negative increment counts down.

**four tickets, four distinct multiples of 10**

```sql
CREATE TEMP TABLE tickets (
  ticket_id INT64 GENERATED ALWAYS AS IDENTITY(START WITH 100 INCREMENT BY 10),
  title STRING
);
INSERT INTO tickets (title) VALUES ('login broken'), ('slow export'), ('typo');
INSERT INTO tickets (title) VALUES ('refund');

SELECT
  COUNT(*) AS tickets,
  COUNT(DISTINCT ticket_id) AS distinct_ids,
  MIN(ticket_id) AS lowest,
  MAX(ticket_id) AS highest,
  LOGICAL_AND(MOD(ticket_id, 10) = 0) AS all_multiples_of_10
FROM tickets
```

Result in BigQuery (1 row):

| tickets | distinct_ids | lowest | highest | all_multiples_of_10 |
| --- | --- | --- | --- | --- |
| 4 | 4 | 100 | 130 | true |

The values are unique and they differ by a multiple of the increment. Google says they are not guaranteed to be in order or without gaps, and that the assignment of values to rows in one statement can vary. So never read a generated value as a row number or a count. The query above checks properties that hold on every run instead of printing the id for each title.

### ALWAYS or BY DEFAULT

`GENERATED ALWAYS` is the mode when you don't say. You can't write the column at all, with a value or with `NULL`. Only `DEFAULT` is accepted:

**inserting 5 into an ALWAYS column**

```sql
CREATE TEMP TABLE tickets (
  ticket_id INT64 GENERATED ALWAYS AS IDENTITY(),
  title STRING
);

INSERT INTO tickets (ticket_id, title) VALUES (5, 'login broken')
```

BigQuery rejects this query:

```text
Query error: Cannot explicitly write to GENERATED ALWAYS identity column 'ticket_id' in INSERT statement at [6:1]
```

An `UPDATE` of that column fails the same way, with `Cannot explicitly write to GENERATED ALWAYS identity column 'id' in UPDATE / MERGE UPDATE statements`.

`GENERATED BY DEFAULT` lets you supply values. `NULL` or `DEFAULT` asks for a generated one. BigQuery does not check the values you supply, so duplicates go in:

**two rows with id 7 in a BY DEFAULT column**

```sql
CREATE TEMP TABLE tickets (
  ticket_id INT64 GENERATED BY DEFAULT AS IDENTITY(),
  title STRING
);
INSERT INTO tickets (ticket_id, title) VALUES (7, 'login broken'), (7, 'slow export');
INSERT INTO tickets (title) VALUES ('typo');

SELECT COUNT(*) AS tickets, COUNT(DISTINCT ticket_id) AS distinct_ids
FROM tickets
```

Result in BigQuery (1 row):

| tickets | distinct_ids |
| --- | --- |
| 3 | 2 |

Three rows, two distinct ids. An identity column is only unique if you use `ALWAYS` or you never write the column yourself. That also means it is not a primary key. See [Primary and foreign keys](https://docs.querylab.io/bigquery/primary-and-foreign-keys/).

### Making a column an identity column

```sql
ALTER TABLE sales.tickets ALTER COLUMN ticket_id
  SET GENERATED BY DEFAULT AS IDENTITY(START WITH 1000 INCREMENT BY 1);
```

The column has to be `INT64`, and old rows keep their values, even `NULL`. Only new rows get generated numbers:

**existing rows are not backfilled**

```sql
CREATE TEMP TABLE tickets (ticket_id INT64, title STRING);
INSERT INTO tickets VALUES (1, 'login broken'), (NULL, 'slow export');

ALTER TABLE tickets ALTER COLUMN ticket_id
  SET GENERATED BY DEFAULT AS IDENTITY(START WITH 1000 INCREMENT BY 1);
INSERT INTO tickets (title) VALUES ('typo');

SELECT ticket_id, title FROM tickets ORDER BY title
```

Result in BigQuery (3 rows):

| ticket_id | title |
| --- | --- |
| 1 | login broken |
| NULL | slow export |
| 1000 | typo |

`ALTER COLUMN ... DROP GENERATED` removes the property and leaves the values. After that you can write the column again.

### Limits

- One identity column per table, and it must be `INT64`.
- You can't partition or cluster by it.
- You can't append to or replace a table that has an identity column with a table copy job, and you can't use a multi-source copy, the Storage Write API or legacy streaming on it. Batch loads, `INSERT` and `MERGE` work. A load into an `ALWAYS` column must leave the column out.
- Legacy SQL can read the table but not write to it.

To see the configuration, `INFORMATION_SCHEMA.COLUMNS` has `identity_generation`, `identity_start` and `identity_increment`. A `BY DEFAULT` column with `START WITH 100 INCREMENT BY 10` reads `BY DEFAULT`, 100 and 10.

## Which to use

For a surrogate key generated on insert, an `ALWAYS` identity column is unique by construction and smaller than a UUID string. For constants and timestamps, use a `DEFAULT`, such as `DEFAULT CURRENT_TIMESTAMP()` on a created-at column.

Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in.

## Sources

Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery:

- [Specify default column values](https://cloud.google.com/bigquery/docs/default-values)

- [Specify an identity column](https://cloud.google.com/bigquery/docs/identity-columns)