This page discusses strategies to use to generate primary key values in yourtable using default value expressions. Information on this page applies to bothGoogleSQL-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 worryabout 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 followingstrategies in a column that hasDEFAULT expressions:

• A UUID function that generates UUID Version 4 values.
• IDENTITY columns that automatically generate integer values for key andnon-key columns.
• SERIAL in PostgreSQL andAUTO_INCREMENT in GoogleSQL,which are DDL aliases forIDENTITY columns.
• A schema object,SEQUENCE, that has abit_reversed_positiveoption.SEQUENCE is available for both GoogleSQL andPostgreSQL.

Universally Unique Identifier (UUID)

Spanner can automatically generate a UUID Version 4 string to useas a primary key. UUIDs work well for new applications and tables with manyrows. They are roughly uniformly distributed across the key space which preventshotspots at scale. UUID generation can create a large number of values(2¹²²) and each value is effectively unique. For example, you wouldneed2.71×10¹⁸ values for a 50% probability of collision, or 1billion per second for 86 years. This ensures unique values when you use it inlarge tables. UUIDs are unique whether you generate them in the database or theclient. We recommend that you use UUIDs when possible. You can safely mixclient- and Spanner-generated UUIDs in the same table if theclient-generated UUIDs are serialized as lower case, in accordance withRFC 4122.

For a column that needs default values, you can use theGENERATE_UUID functionto generate them. The following example shows how to create a table where theFanId key column hasGENERATE_UUID in the value column as its default value.The example uses 36 characters for the GoogleSQLSTRING and PostgreSQLvarchar attributes because UUIDs have 36characters. When you use theINSERT with THEN RETURN statement to insertinto theFans table,GENERATE_UUID generates and returns a UUID value forFanId.

CREATETABLEFans(FanIdSTRING(36)DEFAULT(GENERATE_UUID()),NameSTRING(MAX),)PRIMARYKEY(FanId);

CREATETABLEFans(FanIdvarchar(36)DEFAULTspanner.generate_uuid(),Nametext,PRIMARYKEY(FanId));

INSERTINTOFans(Name)VALUES('Melissa Garcia')THENRETURNFanId;

INSERTINTOfans(name)VALUES('Melissa Garcia')RETURNING(fanid);

This statement returns a result similar to the following:

For more information about theGENERATE_UUID() function, see theGoogleSQLorPostgreSQL reference page.

IDENTITY columns

WithIDENTITY columns, you can automatically generate integer values for keyand non-key columns.IDENTITY columns don't require users to manually maintainan underlying sequence, or manage the relationship between the column and theunderlying sequence. When an auto-generated identity column is dropped, theunderlying sequence is automatically deleted as well.

You can useIDENTITY columns by either providing a starting integer value whengenerating the sequence, or letting Spanner generate theinteger sequence for you. To provide a starting integer value, you mustuse theSTART COUNTER WITH option and use a positiveINT64 starting value.Spanner uses this value to set the next value for itsauto-generated internal sequence counter and bit-reverses the value beforeinserting it into the this column.

In Spanner,IDENTITY columns are supported in bothGoogleSQL and PostgreSQL.

CREATETABLESingers(SingerIdINT64GENERATEDBYDEFAULTASIDENTITY(BIT_REVERSED_POSITIVE),NameSTRING(MAX),RankINT64)PRIMARYKEY(SingerId);

CREATETABLESingers(SingerIdINT64GENERATEDBYDEFAULTASIDENTITY(BIT_REVERSED_POSITIVESTARTCOUNTERWITH1000),NameSTRING(MAX),RankINT64)PRIMARYKEY(SingerId);

CREATETABLESingers(SingerIdbigintGENERATEDBYDEFAULTASIDENTITY(BIT_REVERSED_POSITIVE),Nametext,PRIMARYKEY(SingerId));

CREATETABLESingers(SingerIdbigintGENERATEDBYDEFAULTASIDENTITY(BIT_REVERSED_POSITIVESTARTCOUNTERWITH1000),Nametext,PRIMARYKEY(SingerId));

SERIAL andAUTO_INCREMENT

Spanner supportsSERIAL in PostgreSQL andAUTO_INCREMENT in GoogleSQL which are DDL aliases toIDENTITY columnsand are used for creating unique integer columns. You must first set the databasedefault_sequence_kind option before usingSERIAL orAUTO_INCREMENT.You can use the following SQL statement to set the databasedefault_squence_kindoption:

ALTERDATABASEdbSETOPTIONS(default_sequence_kind='bit_reversed_positive');CREATETABLESingers(idINT64AUTO_INCREMENTPRIMARYKEY,nameSTRING(MAX),)

ALTERDATABASEdbSETspanner.default_sequence_kind='bit_reversed_positive';CREATETABLESingers(idserialPRIMARYKEY,nametext);

Note that sinceSERIAL andAUTO_INCREMENT map to IDENTITY columns, you won'tsee them when you serialize your schema. For this schema, the output ofGetDatabaseDDL would be:

ALTERDATABASEdbSETOPTIONS(default_sequence_kind='bit_reversed_positive');CREATETABLESingers(idINT64GENERATEDBYDEFAULTASIDENTITY,nameSTRING(MAX),)PRIMARYKEY(id);

ALTERDATABASEdbSETspanner.default_sequence_kind='bit_reversed_positive';CREATETABLESingers(idbigintGENERATEDBYDEFAULTASIDENTITYNOTNULL,namecharactervarying,PRIMARYKEY(id));

Bit-reversed sequence

Abit-reversed sequence is a schema object that produces a sequence ofintegers and bit-reverses them. This object uses bit reversal on a private,internal Spanner counter to ensure uniqueness. The resultingbit-reversed valueshelps avoid hotspots at scale when used in a primary key.

In Spanner, you useSEQUENCE DDL statements along with thebit_reversed_positive attribute to create, alter, or drop asequence that produces bit-reversed positive values (GoogleSQL orPostgreSQL).

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

When you define a column with aDEFAULT expression that uses theGoogleSQLGET-NEXT-SEQUENCE-VALUE or the PostgreSQLnextval function as its default value, Spannerautomatically calls the function and puts the bit-reversed output values intothe column. Bit-reversed sequences are especially useful for primary keys,because bit-reversed values are evenly distributed across the key space so thatthey don't cause hotspots.

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

CREATESEQUENCESingerIdSequenceOPTIONS(sequence_kind="bit_reversed_positive");CREATETABLESingers(SingerIdINT64DEFAULT(GET_NEXT_SEQUENCE_VALUE(SEQUENCESingerIdSequence)),NameSTRING(MAX),RankINT64,)PRIMARYKEY(SingerId);

CREATESEQUENCESingerIdSequencebit_reversed_positive;CREATETABLESingers(SingerIdbigintDEFAULTnextval('SingerIdSequence'),Nametext,PRIMARYKEY(SingerId));

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

INSERTINTOSingers(Name)VALUES('Melissa Garcia')THENRETURNSingerId;

INSERTINTOSingers(name)VALUES('Melissa Garcia')RETURNING(SingerId);

This statement returns a result similar to the following:

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

If your existing application requiresINT64 keys in GoogleSQL, orbigint keys in PostgreSQL, Spanner offers thebit-reversed positive sequence schema object (PostgreSQL orGoogleSQL). Otherwise, for new applications, we recommend that youuseUniversally Unique Identifier (UUID).For more information, seeUse a Universally Unique Identifier (UUID).

Migrations

For migrations of tables to Spanner, you have a few options:

• If you are using UUIDs in your source database, on Spanner,you can use a key column in theSTRING type and theGENERATE_UUID()function (GoogleSQL orPostgreSQL) as itsdefault value.
• If you are using an integer primary key, and your application only needs thekey to be unique, you can use a key column inINT64 and use a bit-reversedpositive sequence for the default value for the primary key. SeeMigratingbit-reversed key columns.

• Spanner doesn't support a way to generate monotonic values.

  If you're using a monotonic key, such as the PostgreSQLSERIALtype, or MySQLAUTO_INCREMENT attribute, and you need new monotonic keyson Spanner, you can use a composite key. For moreinformation, seeSwap the order of keys andHash the unique key and spread the writes across logical shards.

• If your application is manually bit-reversing yourINT64 key inGoogleSQL orbigint key in PostgreSQL, you can use abit-reversed positive sequence (GoogleSQL orPostgreSQL) and have it generate new key values for you. Formore information, seeMigrating bit-reversed key columns.

What's next

• Learn more about usingsequences with fine-grained access control.
• Learn about DDLSEQUENCE statements forGoogleSQL orPostgreSQL.
• Learn about sequence functions inGoogleSQL orPostgreSQL.
• Learn about sequences in the INFORMATION_SCHEMA inGoogleSQL orPostgreSQL.
• Learn about sequence options in the INFORMATION_SCHEMA forGoogleSQL.