This page discusses strategies to use to generate primary key values in your
table using default value expressions. Information on this page applies to both
GoogleSQL-dialect databases and PostgreSQL-dialect databases. These strategies have the following benefits:

- Prevent hotspots.
- Simplify migrations from other databases.
- Encapsulate the key logic in the database so that you don't need to worry about managing it in your application.
- In most cases, replace the need to create and manage your own sequences.

## Methods to automatically generate primary keys

To automatically generate primary key values, you can use the following
strategies in a column that has `DEFAULT` expressions:

- A UUID function that generates UUID Version 4 values.
- `IDENTITY` columns that automatically generate integer values for key and non-key columns.
- `SERIAL` in PostgreSQL and `AUTO_INCREMENT` in GoogleSQL, which are data definition language (DDL) aliases for `IDENTITY` columns.
- A schema object, `SEQUENCE`, that has a `bit_reversed_positive` option. `SEQUENCE` is available for both GoogleSQL and PostgreSQL.
- An auto-generated `rowid` column for tables that don't have user-defined primary keys.

### Universally unique identifier (UUID)

Spanner can automatically generate a UUID Version 4 to use as a
primary key. UUIDs work well for new applications and tables with many rows.
They are roughly uniformly distributed across the key space which prevents
hotspots at scale. UUID generation can create a large number of values
(2^122^) and each value is effectively unique. For example, you would
need
2.71×10^18^ values for a 50% probability of collision, or 1
billion per second for 86 years. This wide range ensures unique values when you
use UUIDs in large tables. UUIDs are unique whether you generate them in the
database or the client. We recommend that you use UUIDs when possible. You can
safely mix client-generated and Spanner-generated UUIDs in the
same table if the client-generated UUIDs are serialized as lower case, in
accordance with [RFC 4122](https://tools.ietf.org/html/rfc4122).

For a column that needs default values, you can use the
[`NEW_UUID`](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/utility-functions.md.txt#new_uuid)
GoogleSQL function or the
[`gen_random_uuid()`](https://docs.cloud.google.com/spanner/docs/reference/postgresql/functions.md.txt#utility)
PostgreSQL function to generate them. The following example shows how
to create a table where the `FanId` key column has a generated UUID in the value
column as its default value.

### GoogleSQL

```googlesql
CREATE TABLE Fans (
  FanId UUID DEFAULT (NEW_UUID()),
  Name STRING(MAX),
) PRIMARY KEY (FanId);
```

### PostgreSQL

```postgresql
CREATE TABLE Fans (
  FanId uuid DEFAULT gen_random_uuid(),
  Name text,
  PRIMARY KEY (FanId)
);
```

### GoogleSQL

```googlesql
INSERT INTO Fans (Name) VALUES ('Melissa Garcia')
THEN RETURN FanId;
```

### PostgreSQL

```postgresql
INSERT INTO fans (name) VALUES ('Melissa Garcia')
RETURNING fanid;
```

This statement returns a result similar to the following:

| FanId |
|---|
| 6af91072-f009-4c15-8c42-ebe38ae83751 |

For more information about the generated UUID functions, see the
[GoogleSQL](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/utility-functions.md.txt#new_uuid) or [PostgreSQL](https://docs.cloud.google.com/spanner/docs/reference/postgresql/functions.md.txt#utility) reference page.

### `IDENTITY` columns

With `IDENTITY` columns, you can automatically generate integer values for key
and non-key columns. `IDENTITY` columns don't require that you manually maintain
an underlying sequence, or manage the relationship between the column and the
underlying sequence. When you drop an auto-generated identity column,
Spanner automatically deletes the underlying sequence as well.

You can use `IDENTITY` columns by either providing a starting integer value when
generating the sequence, or letting Spanner generate the integer
sequence for you. To provide a starting integer value, you must use the `START
COUNTER WITH` option and use a positive `INT64` starting value.
Spanner uses this value to set the next value for its
auto-generated internal sequence counter and bit-reverses the value before
inserting it into the this column.

In Spanner, `IDENTITY` columns are supported in both
GoogleSQL and PostgreSQL.

### GoogleSQL

The following example shows how to use `IDENTITY` columns to create an
auto-generated integer primary key column for `SingerId` when creating a new
table using the [`CREATE TABLE`](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/data-definition-language.md.txt#create_table)
command:

```googlesql
CREATE TABLE Singers (
  SingerId INT64 GENERATED BY DEFAULT AS IDENTITY (BIT_REVERSED_POSITIVE),
  Name STRING(MAX),
  Rank INT64
) PRIMARY KEY (SingerId);
```

You can also specify the counter start for the column using the
`START COUNTER WITH` option. In the following example, an auto-generated
integer column is created for `SingerId` that has bit-reversed positive
values and an internal counter that starts at
1,000.

```googlesql
CREATE TABLE Singers (
  SingerId INT64 GENERATED BY DEFAULT AS IDENTITY (BIT_REVERSED_POSITIVE START COUNTER WITH 1000),
  Name STRING(MAX),
  Rank INT64
) PRIMARY KEY (SingerId);
```

### PostgreSQL

The following example shows how to use `IDENTITY` columns to create an
auto-generated integer column for `SingerId` when creating a new table using
the [`CREATE
TABLE`](https://docs.cloud.google.com/spanner/docs/reference/postgresql/data-definition-language#create_table)
command:

```postgresql
CREATE TABLE Singers (
  SingerId bigint GENERATED BY DEFAULT AS IDENTITY (BIT_REVERSED_POSITIVE),
  Name text,
  PRIMARY KEY (SingerId)
);
```

You can also specify the counter start for the column using the `START
COUNTER WITH` option. In the following example, an auto-generated integer
column is created for `SingerId` which generates bit-reversed positive
values and the internal counter, before bit-reversing, starts from
1,000.

```postgresql
CREATE TABLE Singers (
  SingerId bigint GENERATED BY DEFAULT AS IDENTITY (BIT_REVERSED_POSITIVE START COUNTER WITH 1000),
  Name text,
  PRIMARY KEY (SingerId)
);
```

### `SERIAL` and `AUTO_INCREMENT`

Spanner supports `SERIAL` in PostgreSQL and
`AUTO_INCREMENT` in GoogleSQL which are DDL aliases to [`IDENTITY`
columns](https://docs.cloud.google.com/spanner/docs/primary-key-default-value#identity-columns) and are used for creating unique integer columns.
You must first set the database `default_sequence_kind` option before using
`SERIAL` or `AUTO_INCREMENT`. You can use the following SQL statement to set the
database `default_sequence_kind` option:

### GoogleSQL

```googlesql
ALTER DATABASE db SET OPTIONS (default_sequence_kind = 'bit_reversed_positive');

CREATE TABLE Singers (
  id INT64 AUTO_INCREMENT PRIMARY KEY,
  name STRING(MAX),
);
```

### PostgreSQL

```postgresql
ALTER DATABASE db SET spanner.default_sequence_kind = 'bit_reversed_positive';

CREATE TABLE Singers (
  id serial PRIMARY KEY,
  name text
);
```

Because `SERIAL` and `AUTO_INCREMENT` map to IDENTITY columns, you won't see
them when you serialize your schema. For this schema, the output of
`GetDatabaseDDL` would be:

### GoogleSQL

```googlesql
ALTER DATABASE db SET OPTIONS (default_sequence_kind = 'bit_reversed_positive');

CREATE TABLE Singers (
  id INT64 GENERATED BY DEFAULT AS IDENTITY,
  name STRING(MAX)
) PRIMARY KEY (id);
```

### PostgreSQL

```postgresql
ALTER DATABASE db SET spanner.default_sequence_kind = 'bit_reversed_positive';

CREATE TABLE Singers (
  id bigint GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  name character varying,
  PRIMARY KEY(id)
);
```

### Bit-reversed sequence

A *bit-reversed sequence* is a schema object that produces a sequence of
integers and bit-reverses them. This object uses bit reversal on a private,
internal Spanner counter to ensure uniqueness. The resulting
[bit-reversed values](https://docs.cloud.google.com/spanner/docs/schema-design.md.txt#bit_reverse_primary_key) helps
avoid hotspots at scale when used in a primary key.

In Spanner, you use `SEQUENCE` DDL statements along with the
`bit_reversed_positive` attribute to create, alter, or drop a sequence that
produces bit-reversed positive values ([GoogleSQL](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/data-definition-language.md.txt#sequence_statements) or [PostgreSQL](https://docs.cloud.google.com/spanner/docs/reference/postgresql/data-definition-language.md.txt#sequence_statements)).

Each sequence maintains a set of internal counters and uses them to generate a
value. The sequence counter provides the input to the bit-reversing algorithm.

When you define a column with a `DEFAULT` expression that uses the
GoogleSQL `GET_NEXT_SEQUENCE_VALUE` or the PostgreSQL
`nextval` function as its default value, Spanner
automatically calls the function and puts the bit-reversed output values into
the column. Bit-reversed sequences are especially useful for primary keys,
because bit-reversed values are evenly distributed across the key space so that
they don't cause hotspots.

The following example shows how to create a bit-reversed sequence and a table
where its key column uses the sequence as the default value:

### GoogleSQL

```googlesql
CREATE SEQUENCE SingerIdSequence OPTIONS (
  sequence_kind="bit_reversed_positive"
);

CREATE TABLE Singers (
  SingerId INT64 DEFAULT (GET_NEXT_SEQUENCE_VALUE(SEQUENCE SingerIdSequence)),
  Name STRING(MAX),
  Rank INT64
) PRIMARY KEY (SingerId);
```

### PostgreSQL

```postgresql
CREATE SEQUENCE SingerIdSequence bit_reversed_positive;

CREATE TABLE Singers (
  SingerId bigint DEFAULT nextval('SingerIdSequence'),
  Name text,
  PRIMARY KEY (SingerId)
);
```

You can then use the following SQL statement to insert and return the primary
key value:

### GoogleSQL

```googlesql
INSERT INTO Singers (Name) VALUES ('Example Singer')
THEN RETURN SingerId;
```

### PostgreSQL

```postgresql
INSERT INTO Singers (name) VALUES ('Example Singer')
RETURNING SingerId;
```

This statement returns a result similar to the following:

| SingerId |
|---|
| 3458764513820540928 |

### Create a table without defining a primary key

If you don't provide any primary keys when creating a table, then
Spanner creates a hidden column named `rowid`. This column
serves as the primary key. By default, the `rowid` column is an
[`IDENTITY`](https://docs.cloud.google.com/spanner/docs/primary-key-default-value.md.txt#identity-columns)
column that uses `INT64` values and is backed by a
[bit-reversed sequence](https://docs.cloud.google.com/spanner/docs/primary-key-default-value.md.txt#bit-reversed-sequence) to automatically generate
keys.

You can create a table without defining primary keys in the following way:

### GoogleSQL

```googlesql
CREATE TABLE Singers (
  Name STRING(MAX),
  Rank INT64
);
```

### PostgreSQL

```postgresql
CREATE TABLE Singers (
  Name text,
  Rank bigint
);
```

The resulting schema is shown as follows:

### GoogleSQL

```googlesql
CREATE TABLE Singers (
  Name STRING(MAX),
  Rank INT64,
  rowid INT64 NOT NULL GENERATED BY DEFAULT AS IDENTITY
    (BIT_REVERSED_POSITIVE) HIDDEN
) PRIMARY KEY (rowid);
```

### PostgreSQL

```postgresql
CREATE TABLE Singers (
  Name text,
  Rank bigint,
  rowid bigint GENERATED BY DEFAULT AS IDENTITY
    (BIT_REVERSED_POSITIVE) NOT NULL HIDDEN,
  PRIMARY KEY (rowid)
);
```

You can't add a primary key to a table that you previously created without
defining a primary key.

Because Spanner doesn't allow two columns with the same name in a
table, you cannot define a new non-key column named `rowid` in a table that you
created earlier without defining primary keys. The following query returns an
error:

### GoogleSQL

```googlesql
CREATE TABLE Singers (
  rowid INT64,
  Name STRING(MAX),
);
```

### PostgreSQL

```postgresql
CREATE TABLE Singers (
  rowid bigint,
  Name text
);
```

Spanner sets the `rowid` column to `HIDDEN`. This means that the
column doesn't appear in a `SELECT *` query:

### GoogleSQL

```googlesql
SELECT * FROM Singers;

/*---+---+
 | Name  | Rank |
 +---+---+
 | Alice | 1    |
 | David | 2    |
 +---+---*/
```

### PostgreSQL

```postgresql
SELECT * FROM Singers;

/*---+---+
 | Name  | Rank |
 +---+---+
 | Alice | 1    |
 | David | 2    |
 +---+---*/
```

However, you can explicitly query the hidden `rowid` column:

### GoogleSQL

```googlesql
SELECT rowid FROM Singers;

/*---+
 | rowid               |
 +---+
 | 3458764513820540928 |
 +---*/
```

### PostgreSQL

```postgresql
SELECT rowid FROM Singers;

/*---+
 | rowid               |
 +---+
 | 3458764513820540928 |
 +---*/
```

Although the `rowid` column doesn't appear in a `SELECT *` query, the `rowid`
column is visible in the `INFORMATION_SCHEMA` tables. For example, you can run
the following query:

### GoogleSQL

```googlesql
SELECT column_name
FROM information_schema.key_column_usage
WHERE constraint_name LIKE 'PK_%' AND table_name = 'Singers';

/*---+
 | column_name |
 +---+
 | rowid       |
 +---*/
```

### PostgreSQL

```postgresql
SELECT column_name
FROM information_schema.key_column_usage
WHERE constraint_name LIKE 'PK_%' AND table_name = 'singers';

 /*---+
 | column_name |
 +---+
 | "rowid"     |
 +---*/
```

## Scenarios for using UUIDs and sequences as default values for primary keys

The scenarios for UUIDs and sequences include the following:

- New applications
- Migrations

The following sections describe each scenario.

### New applications

For new applications, Spanner recommends using [universally
unique identifier (UUID)](https://docs.cloud.google.com/spanner/docs/primary-key-default-value#universally_unique_identifier_uuid) for primary keys.
For more information, see [Use a universally unique identifier (UUID)](https://docs.cloud.google.com/spanner/docs/schema-design.md.txt#uuid_primary_key).

If your existing application requires `INT64` keys in GoogleSQL, or
`bigint` keys in PostgreSQL, Spanner offers the
bit-reversed positive sequence schema object ([PostgreSQL](https://docs.cloud.google.com/spanner/docs/reference/postgresql/data-definition-language.md.txt#sequence_statements) or [GoogleSQL](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/data-definition-language.md.txt#sequence_statements)).

### Migrations

To migrate tables to Spanner, you have the following options:

- If you are using UUIDs in your source database, on Spanner, you can use a UUID type column and the generated UUID function ([GoogleSQL](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/utility-functions.md.txt#new_uuid) or [PostgreSQL](https://docs.cloud.google.com/spanner/docs/reference/postgresql/functions.md.txt#utility)) as its default value.
- If you are using an integer primary key, and your application only needs the key to be unique, you can use a key column in `INT64` and use a bit-reversed positive sequence for the default value for the primary key. See [Migrating
  bit-reversed key columns](https://docs.cloud.google.com/spanner/docs/migrating-primary-keys#migrate-sequential-key-columns).
- If you're using a monotonic key, such as the PostgreSQL `SERIAL` type, or MySQL `AUTO_INCREMENT` attribute, and you need new monotonic keys on Spanner, you can use a composite key because Spanner doesn't support a way to generate monotonic values. For more information, see [Swap the order of keys](https://docs.cloud.google.com/spanner/docs/schema-design.md.txt#fix_swap_key_order) and [Hash the unique
  key and spread the writes across logical shards](https://docs.cloud.google.com/spanner/docs/schema-design#fix_hash_the_key).
- If your application is manually bit-reversing your `INT64` key in GoogleSQL or `bigint` key in PostgreSQL, you can use a bit-reversed positive sequence ([GoogleSQL](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/data-definition-language.md.txt#sequence_statements) or [PostgreSQL](https://docs.cloud.google.com/spanner/docs/reference/postgresql/data-definition-language.md.txt#sequence_statements)) and have it generate new key values for you. For more information, see [Migrating bit-reversed key columns](https://docs.cloud.google.com/spanner/docs/migrating-primary-keys.md.txt#migrate-bit-reversed-key-columns).

## What's next

- Learn more about using [sequences with fine-grained access control](https://docs.cloud.google.com/spanner/docs/fgac-sequences.md.txt).
- Learn about DDL `SEQUENCE` statements for [GoogleSQL](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/data-definition-language.md.txt#sequence_statements) or [PostgreSQL](https://docs.cloud.google.com/spanner/docs/reference/postgresql/data-definition-language.md.txt#sequence_statements).
- Learn about sequence functions in [GoogleSQL](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/sequence_functions.md.txt) or [PostgreSQL](https://docs.cloud.google.com/spanner/docs/reference/postgresql/functions-and-operators#sequence).
- Learn about sequences in the `INFORMATION_SCHEMA` in [GoogleSQL](https://docs.cloud.google.com/spanner/docs/information-schema.md.txt#sequences) or [PostgreSQL](https://docs.cloud.google.com/spanner/docs/information-schema-pg.md.txt#sequences).
- Learn about sequence options in the `INFORMATION_SCHEMA` for [GoogleSQL](https://docs.cloud.google.com/spanner/docs/information-schema.md.txt#sequence_options).