{{}} The Primary key is a column or a set of columns that uniquely identifies a row, such as a user ID or order number. You should choose the primary key based on the most common access pattern. Columns of data type [string](../../../explore/ycql-language/data-types/#strings), [number](../../../explore/ycql-language/data-types/#numeric-types), [serial](../../../explore/ysql-language-features/data-types/#serial-pseudotype), or [UUID](../../../explore/ycql-language/data-types/#universally-unique-id-types) make good choices for primary keys. ## Automatically generating the primary key The best way to uniquely identify a record is to allow the database to assign a unique identifier to the row. YugabyteDB supports multiple schemes for generating identifiers that you can choose based on the needs of your application. ### UUID A UUID is a 128-bit number represented as a string of 36 characters, including hyphens. For example, `4b6aa2ff-53e6-44f5-8bd0-ef9de90a8095`. YugabyteDB natively supports [UUID](https://en.wikipedia.org/wiki/Universally_unique_identifier) generation as per [RFC 4122](https://datatracker.ietf.org/doc/html/rfc4122) via the uuid-ossp extension. UUIDs have several advantages: - The likelihood of generating duplicate UUIDs is extremely low. - UUIDs can be independently generated on different nodes in the cluster without any coordination with other systems. - The randomness of UUIDs makes it hard to predict the next ID, providing an additional layer of security. You can add a UUID to your schema as follows: ```sql CREATE TABLE users ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), name TEXT ); ``` The [DEFAULT](../../../api/ysql/the-sql-language/statements/ddl_create_table/#default) clause ensures that for every row inserted, a UUID is automatically generated and inserted along with the row. ### Serial [Serial](../../../api/ysql/datatypes/type_serial/) is a special data type in YugabyteDB that creates an auto-incrementing integer column starting with `1`. It is essentially a shorthand for creating a sequence and using it as a default value for a column. You can choose between three types of serial data types depending on the needs of your application: - **SMALLSERIAL** - An integer column in the range of 1 to 32,767. - **SERIAL** - An integer column in the range of 1 to 2,147,483,647. - **BIGSERIAL** - An integer column in the range of 1 to 9,223,372,036,854,775,807. Serial can be used directly in table definitions to simplify the creation of auto-incrementing columns. ```sql DROP TABLE IF EXISTS users; CREATE TABLE users ( id serial, name TEXT, PRIMARY KEY(id) ); ``` For each row inserted into the table, an auto-incremented `id` value is automatically inserted along with the row. ### Sequence A [sequence](../../../api/ysql/the-sql-language/statements/ddl_create_sequence/) is a database object that generates a sequence of unique numbers. Sequences are independent objects that can be associated with one or more tables or columns. Sequences offer more flexibility and control over auto-incrementing behavior. They can be created, managed, and used separately from table definitions. Sequences can be customized with different increment values, start values, minimum and maximum values, and cycle behavior. ```sql CREATE SEQUENCE user_id_seq START 100 INCREMENT BY 100 CACHE 10000; DROP TABLE IF EXISTS users; CREATE TABLE users ( id INTEGER DEFAULT nextval('user_id_seq'), name TEXT, PRIMARY KEY(id) ); ``` For every row inserted, user IDs are automatically generated as 100, 200,300, and so on. {{}} Use serial for basic use cases and opt for sequences when you need more control over the sequence behavior, need to share a sequence between multiple tables or columns, or require custom incrementing logic. {{}} ## Existing columns as primary keys To illustrate how to choose existing columns as primary keys, first create a sample census schema. {{