# CREATE TABLE with an AUTO\_INCREMENT Column `AUTO_INCREMENT` can be used to automatically generate a unique value for new rows. When you insert a new row, and the `AUTO_INCREMENT` field is `DEFAULT`, `NULL`, or `0`, SingleStore automatically assigns a value. This value cannot be set to 0. It’s important to understand that `AUTO_INCREMENT` only guarantees that automatically-generated values are unique. In general, it does not guarantee that they: * are consecutive or sequential * are monotonically increasing * start from any particular value * are distinct from explicitly-set values If you explicitly set a value in an `INSERT` or `UPDATE` statement, **it may collide with past or future automatically-generated values**. For example, in the following example, a value is added explicitly to the `AUTO_INCREMENT` column. This will break the table unless `AGGREGATOR SYNC AUTO_INCREMENT` is run by the end user (which will reset the auto-increment counter to a value higher than 10). In this case, a duplicate key will not be generated when the `AUTO_INCREMENT` value reaches 10 eventually on its own as additional rows are added to the table. ```sql CREATE TABLE ct_auto (c1 INT AUTO_INCREMENT PRIMARY KEY); INSERT INTO ct_auto (c1) VALUES (10); ``` A table can have only one `AUTO_INCREMENT` column. The `AUTO_INCREMENT` column must be included in an index (not necessarily a `PRIMARY` or `UNIQUE` key, a regular key is also allowed). If syntax such as `CREATE TABLE table_1 SELECT * FROM table_2` is used to create `table_1` where `table_2` has an `AUTO_INCREMENT` column, it will be created as a non-auto-increment column in `table_1`. See [LAST\_INSERT\_ID](https://docs.singlestore.com/db/v9.1/reference/sql-reference/information-functions/last-insert-id.md) for more information on `AUTO_INCREMENT` behavior. If the `AUTO_INCREMENT` behavior described here does not satisfy your requirements you can create your own sequence generator using `LAST_INSERT_ID`. See the [sequence generator stored procedure example](https://docs.singlestore.com/db/v9.1/reference/sql-reference/information-functions/last-insert-id.md). > **⚠️ Warning**: Restarting an aggregator, such as during upgrades or host machine maintenance, will introduce a large gap between any `AUTO_INCREMENT` values inserted before the restart and any values inserted after. In the case of reference tables, this same behavior might also occur when a child aggregator is promoted to master aggregator. Depending on how often you restart your aggregators, you could see many jumps in values from a specific aggregator. These jumps are because each aggregator defines and manages its own range of values to start incrementing from to prevent collisions in a table. With each restart, a new batch of values is used. For sharded tables, the range of `AUTO_INCREMENT` values increases to the next 1,000,000 after each restart (e.g. 2,430,403 before restart -> 3,000,000 after). For reference tables, the batch size jumps to the next 1,000. ## AUTO\_INCREMENT in Sharded Tables On a sharded (distributed) table, `AUTO_INCREMENT` can only be used on a `BIGINT` column (as they usually use the entire 64 bits). Each aggregator computes and tracks its own `AUTO_INCREMENT` values and uses those values when new rows are added to a table. The values on each aggregator are usually, but not always, sequential; therefore, inserts on an individual aggregator generate values which are unique and usually sequential. And because each aggregator manages its own `AUTO_INCREMENT` values, the automatically-generated values from inserts across multiple aggregators are only unique, never sequential. > **📝 Note**: If an `AUTO_INCREMENT` column (without the `SEQUENCE` modifier) in a sharded table is declared with a smaller integer type, it is implicitly converted to `BIGINT`. Here is an example to illustrate how `AUTO_INCREMENT` values are generated across aggregators in a cluster as new rows are inserted into table `ct_tb`: ```sql SELECT * FROM ct_tb ORDER BY b; ``` ```output +-------------------+------+------------+ | a | b | c | +-------------------+------+------------+ | 1 | 1 | from MA | | 2 | 2 | from MA | | 3 | 3 | from MA | | 4 | 4 | from MA | | 5 | 5 | from MA | | 13510798882111489 | 6 | from CA 96 | | 13510798882111490 | 7 | from CA 96 | | 13510798882111491 | 8 | from CA 96 | | 13510798882111492 | 9 | from CA 96 | | 13510798882111493 | 10 | from CA 96 | | 14636698788954113 | 11 | from CA 20 | | 14636698788954114 | 12 | from CA 20 | | 14636698788954115 | 13 | from CA 20 | | 14636698788954116 | 14 | from CA 20 | | 14636698788954117 | 15 | from CA 20 | | 6 | 16 | from MA | | 15762598695796737 | 17 | from CA 17 | | 13510798882111494 | 18 | from CA 96 | | 7 | 19 | from MA | | 14636698788954118 | 20 | from CA 20 | +-------------------+------+------------+ ``` As shown in the example above, automatically-generated `AUTO_INCREMENT` values can differ depending on which aggregator you run the inserts on. Of course, if you ran some inserts on one aggregator and some inserts on another aggregator, you would get different automatically generated values. Also note that automatically-generated values and explicitly-set values can collide in sharded tables. ## AUTO\_INCREMENT in Reference Tables The `AUTO_INCREMENT` value for a reference table is tracked by the master aggregator. It is guaranteed that the next `AUTO_INCREMENT` value will always be greater than any value previously seen in this column. These generated values are usually sequential, but not always. Contrarily to the behavior for sharded tables, explicitly setting a value in an `INSERT` or `UPDATE` statement will not create a collision with future automatically generated values. The next example shows some queries using `AUTO_INCREMENT` fields on reference tables. ```sql CREATE REFERENCE TABLE ct_ref_1(id INT AUTO_INCREMENT PRIMARY KEY); INSERT INTO ct_ref_1 VALUES(); INSERT INTO ct_ref_1 VALUES(5); INSERT INTO ct_ref_1 VALUES(); ``` ```sql SELECT id FROM ct_ref_1 ORDER BY id; ``` ```output +----+ | id | +----+ | 1 | | 5 | | 6 | +----+ ``` ```sql UPDATE ct_ref_1 SET id = 9 WHERE id = 5; INSERT INTO ct_ref_1(); ``` ```sql SELECT id FROM ct_ref_1 ORDER BY id; ``` ```output +----+ | id | +----+ | 1 | | 6 | | 9 | | 10 | +----+ ``` ```sql DELETE FROM ct_ref_1; INSERT INTO ct_ref_1 VALUES(); ``` ```sql SELECT id FROM ct_ref_1 ORDER BY id; ``` ```output +----+ | id | +----+ | 11 | +----+ ``` ## Setting AUTO\_INCREMENT Starting Values It is possible to override the starting `AUTO_INCREMENT` value for reference tables by setting the `AUTO_INCREMENT` option on a `CREATE TABLE` statement. The starting value cannot be set to 0. The following example shows how to set the `AUTO_INCREMENT` start value during table creation: ```sql CREATE REFERENCE TABLE ct_ref_2 (id int AUTO_INCREMENT PRIMARY KEY) AUTO_INCREMENT = 7; INSERT INTO ct_ref_2 VALUES (), (); ``` ```sql SELECT * FROM ct_ref_2; ``` ```output +----+ | id | +----+ | 7 | | 8 | +----+ ``` This syntax has no effect on sharded tables. It will not return an error, for compatibility with external tools, but it will explicitly present a warning and no operation will be done. ## AUTO\_INCREMENT During Replication When replicating data between clusters, the secondary cluster has all the replicated `AUTO_INCREMENT` values from the primary cluster. When you failover to a secondary cluster, SingleStore synchronizes the secondary cluster by looking for the maximum value in the range of `AUTO_INCREMENT` values on every aggregator. ```sql CREATE TABLE IF NOT EXISTS my_MemSQL_table (id INT PRIMARY KEY AUTO_INCREMENT, v VARCHAR(10) NOT NULL); ``` ## AUTO\_INCREMENT AS SEQUENCE The `SEQUENCE` modifier ("the sequence" or "Sequences") for `AUTO_INCREMENT` ensures generation of a unique sequence of numbers while greatly reducing the difference between the generated numbers. Additionally, the `SEQUENCE` modifier allows you to set the column type for `AUTO_INCREMENT` columns to other integer types. ## SEQUENCE Behavior When the `SEQUENCE` modifier is specified, each aggregator pre-fetches a range of sequences and reserves the range in cache. For every request, the aggregator returns values from the cache in order. Consequently, inserts via different aggregators, although unique, are not sequential. For example, if the cache size is `200` and the SingleStore deployment has two aggregators, the insert on the first aggregator inserts `1` and the insert on the second aggregator inserts `201` as follows: ```sql -- Create a table with AUTO_INCREMENT AS SEQUENCE column -- CREATE TABLE stock ( stockID BIGINT AUTO_INCREMENT AS SEQUENCE PRIMARY KEY, Code CHAR(4)); -- Insert via aggregator 1, cached values in the range 1-200, inserts 1 -- INSERT INTO stock (Code) VALUES ('AG01'); -- Insert via aggregator 2, cached values in the range 201-400, inserts 201 -- INSERT INTO stock (Code) VALUES ('AG02'); -- Query the table -- SELECT * FROM stock ORDER BY stockID; ``` ```output +---------+------+ | stockID | Code | +---------+------+ | 1 | AG01 | | 201 | AG02 | +---------+------+ ``` The same applies to different transactions running on the same aggregator. Refer to [Configure Sequences](https://docs.singlestore.com/#section-idm32770100399602361.md) for engine variables used to configure the cache size. If the `SEQUENCE` modifier is specified for an `AUTO_INCREMENT` column, synchronization of the sequence counter across aggregators is not required unless manual inserts are performed to correct the sequence (which requires synchronization). Refer to [Example: Update the Sequence after Manual Insert](https://docs.singlestore.com/#section-idm32574462884691831.md) and [Example: Reset the Current Sequence Value](https://docs.singlestore.com/#section-idm32658053459244512.md) for more information. ## Key Benefits of Using the SEQUENCE Modifier The `SEQUENCE` modifier provides the following improvements over `AUTO_INCREMENT`: * Generates **unique** sequential numbers and greatly reduces the difference between the generated numbers. * Auto-synchronization of the sequence across aggregators is not required (unless manual inserts or updates to the sequence are performed). * Supports any integer type for column type (`AUTO_INCREMENT` only supports the `BIGINT` type). * Allows specifying a starting value for the sequence and resetting or updating the current sequence value to a specific number. ## Create a Column with AUTO\_INCREMENT AS SEQUENCE Use the following syntax to create an `AUTO_INCREMENT` column with the `SEQUENCE` modifier: ```sql CREATE TABLE (table_name) ( column_name AUTO_INCREMENT AS SEQUENCE [ ] ) [ AUTO_INCREMENT= ] ``` where, * `` specifies the column type. Columns with the `SEQUENCE` modifier support any integer type. * `AUTO_INCREMENT=` specifies a custom starting value for the sequence. * `index/key` specifies the type of index or key. > **📝 Note**: The `AUTO_INCREMENT AS SEQUENCE` column must be included in an index (not necessarily a `PRIMARY` or `UNIQUE` key, a regular key is also allowed). For example, the following command creates an `AUTO_INCREMENT` column `orderID` with the `SEQUENCE` modifier and specifies a custom starting value for the sequence: ```sql CREATE TABLE orders ( orderID BIGINT AUTO_INCREMENT AS SEQUENCE PRIMARY KEY, customerID CHAR(4) ) AUTO_INCREMENT = 1000; INSERT INTO orders (customerID) VALUES ('AA01'), ('AAO2'); SELECT * FROM orders; ``` ```output +---------+------------+ | orderID | customerID | +---------+------------+ | 1000 | AA01 | | 1001 | AAO2 | +---------+------------+ ``` ## Reset or Update the Current Sequence Value Use the following syntax to reset the current value of the sequence to a custom value: ```sql ALTER TABLE AUTO_INCREMENT = ; ``` > **📝 Note**: The `ALTER TABLE ... AUTO_INCREMENT` command does not validate whether the specified value exists or is lower than the current maximum value in the table. Specifying a value that already exists or is lower than the current maximum sequence value may result in `Duplicate entry '' for key 'PRIMARY'` error on insert operations. Alternatively, run the following command to reset the sequence counter to a minimum of ` + 1`: ```sql AGGREGATOR SYNC AUTO_INCREMENT ON . ALL; ``` Additionally, **if manual inserts are performed in a table** with a `SEQUENCE` column, run the `AGGREGATOR SYNC AUTO_INCREMENT` command to reset the sequence counter. SingleStore recommends running this command to prevent duplicate key errors on insert. Because the sequence must synchronize to all the aggregators at once, only `AGGREGATOR SYNC AUTO_INCREMENT ... ALL` statements are supported on tables with `SEQUENCE` columns. ## Example: Update the Sequence after Manual Insert The following example shows how to synchronize the current sequence value after a manual update to the sequence column and the sequence behavior during this operation. In the `orders` table created earlier: 1. Manually insert a few rows with custom values for the `SEQUENCE` column: ```sql INSERT INTO orders VALUES (1100, 'AB10'), (1200, 'AC10'); ``` 2. Synchronize the sequence across aggregators: ```sql AGGREGATOR SYNC AUTO_INCREMENT ON dbTest.orders ALL; ``` 3. Insert row(s) using the automatically generated sequence: ```sql INSERT INTO orders (customerID) VALUES ('BA01'), ('BA02'); ``` 4. Query the values in the `orders` table: ```sql SELECT * FROM orders ORDER BY orderID; ``` ```output +---------+------------+ | orderID | customerID | +---------+------------+ | 1000 | AA01 | | 1001 | AAO2 | | 1100 | AB10 | | 1200 | AC10 | | 1201 | BA01 | | 1202 | BA02 | +---------+------------+ ``` Notice that the current sequence value was reset to "the highest value in the sequence column after the manual insert plus one" (`1200 + 1`), and the new rows added with the automatically generated sequence have greater values (`1201+`). ## Example: Reset the Current Sequence Value To manually reset the sequence: 1. Run the `ALTER TABLE ... AUTO_INCREMENT` command as: ```sql ALTER TABLE orders AUTO_INCREMENT = 1500; ``` 2. The next `INSERT` statement uses sequence values from `1500`. For example: ```sql INSERT INTO orders (customerID) VALUES ('CA01'); SELECT * FROM orders ORDER BY orderID; ``` ```output +---------+------------+ | orderID | customerID | +---------+------------+ | 1000 | AA01 | | 1001 | AAO2 | | 1100 | AB10 | | 1200 | AC10 | | 1201 | BA01 | | 1202 | BA02 | | 1500 | CA01 | +---------+------------+ ``` ## SEQUENCE Remarks * Only one sequence column is allowed for each table. * Sequences are not supported in reference or temporary tables. To generate unique values on reference tables, use `AUTO_INCREMENT` (without the `SEQUENCE` modifier). Refer to [AUTO\_INCREMENT in Reference Tables](https://docs.singlestore.com/#section-idm32186744900596417.md) for more information. * The `AUTO_INCREMENT AS SEQUENCE` column must be included in an index (not necessarily a `PRIMARY` or `UNIQUE` key, a regular key is also allowed). * The `AGGREGATOR SYNC AUTO_INCREMENT ON ALL` command does not guarantee the correctness of the sequence value if inserts are being performed on the table concurrently. ## Configure Sequences Use the following engine variables to configure Sequences (refer to the [list of engine variables](https://docs.singlestore.com/db/v9.1/reference/configuration-reference/engine-variables/list-of-engine-variables.md) for more information including default values): | Variable Name | Description | | ------------------------------------ | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `sequence_min_cache_count` | Minimum number of sequence values pre-fetched by an aggregator. | | `sequence_max_cache_count` | Maximum size the pre-fetch cache can reach after`sequence_cache_resize_up_time_us`. | | `sequence_cache_resize_up_time_us` | Specifies a time interval in microseconds. If cache requests arrive more frequently than the specified time interval,SingleStoreincreases the cache size for the sequence by 2x. | | `sequence_cache_resize_down_time_us` | Specifies a minimum time interval (in microseconds) that must elapse between cache requests beforeSingleStoredowngrades the cache size for the sequence by 2x. | The `sequence_cache_resize_up_time_us` and `sequence_cache_resize_down_time_us` time is measured at the owner partition, taking into account requests from all aggregators. This ensures that the system evaluates global demand, before increasing or decreasing cache size. ## AUTO\_INCREMENT Data Type Requirements in Client Applications When reading `AUTO_INCREMENT` values into client applications, ensure that the application-side data type can store the entire range of values generated by an `AUTO_INCREMENT` column. For example, in sharded tables, `AUTO_INCREMENT` can only be used on a `BIGINT` column and generated identifiers typically use the full 64-bit range. If an `AUTO_INCREMENT` column (without the `SEQUENCE` modifier) in a sharded table is declared with a smaller integer type, it is implicitly converted to `BIGINT`. Values that are truncated or rounded when read into a smaller or floating-point type may fail to correctly match the originally generated `AUTO_INCREMENT` value and can consequently lead to the following: * duplicate key errors when they are written back to the database, or * client-side overflow errors or other loss of information in the client application. If your application uses ORMs, frameworks, or runtime libraries that abstract underlying SQL types, verify that the mapped type used for `AUTO_INCREMENT` identifiers can hold the generated values. > **⚠️ Caution**: Although `AUTO_INCREMENT` values may initially fall in the range of a smaller integer type, they can eventually grow to require the full 64-bit range. Therefore, applications must use a data type that supports 64-bit integer values to prevent overflow-related errors in production environments.Do not use the following types in client applications to store auto-increment values:- 32-bit integer types > - Double-precision floating-points types > - `number` type in JavaScript or TypeScript The following table specifies the recommended type for common programming languages: | Language | Recommended Data Type | | --------------- | ------------------------------------------------------- | | JavaScript | `bigint`(the`BigInt`primitive type) | | Python | `int` | | C/C++ | `int64_t`(from``) | | C#/.NET | `long`(`System.Int64`) | | Go | `int64` | | Rust | `i64` | | Java | `long` | | Other languages | Signed integer type with at least 64 bits of precision. | If the application code or framework does not support 64-bit identifiers or if smaller identifier types are preferred, use the following: * **Use AUTO\_INCREMENT AS SEQUENCE**: A column defined as [AUTO\_INCREMENT AS SEQUENCE](https://docs.singlestore.com/#section-idm32307684455268381.md) can use any integer type, which allows you to configure a smaller data type while still using sequence semantics. However, you must ensure the following: * The table row count does not exceed the maximum value supported by the selected integer type. * Gaps introduced in the sequence (for example, from restarts, manual inserts, or sequence resets) do not exhaust the available integer range earlier than expected. * **Generate identifiers in the application logic**: Generate primary key values within the application using a scheme compatible with the application’s preferred identifier type. In this approach, `AUTO_INCREMENT` is not used for the primary key, and the application is responsible for ensuring uniqueness, handling concurrency, and preventing identifier conflicts. SingleStore recommends using 64-bit integer types for `AUTO_INCREMENT` columns and in client applications to avoid precision loss, identifier range conflicts, and duplicate key errors. ## AUTO\_INCREMENT Best Practices ## AUTO\_INCREMENT On Sharded Tables On sharded tables, define the `AUTO_INCREMENT` column as `BIGINT`. Each aggregator maintains and allocates values independently; therefore, expect large, non-sequential values and substantial gaps, especially following either of the following operations: * Syncing auto-increment values after a manual insert or update * Node restart > **📝 Note**: `AUTO_INCREMENT` gaps are expected on sharded tables and do not indicate data loss. When large, sparse auto-increment values are acceptable, use `AUTO_INCREMENT` without `SEQUENCE`. For smaller gaps and near-sequential number allocation, use `AUTO_INCREMENT AS SEQUENCE`, which also supports smaller integer types. However, using smaller integer types for `SEQUENCE` columns may lead to premature exhaustion of values on high-volume tables. Ensure [AUTO\_INCREMENT Data Type Requirements in Client Applications](https://docs.singlestore.com/#section-id235570472471597.md) are met. ## Manual Inserts, Updates, or Deletes Avoid manual inserts or updates to the `AUTO_INCREMENT` column. If values are manually inserted or updated in the `AUTO_INCREMENT` column, run `AGGREGATOR SYNC AUTO_INCREMENT` to sync the auto-increment values across all the aggregators and reset the counter to a value greater than the current maximum. ```sql AGGREGATOR SYNC AUTO_INCREMENT [ON [.
]] ALL; ``` This command is not required for delete operations because SingleStore does not reuse auto-increment values by default. Do not manually reuse auto-increment values after a delete operation. Gaps are expected and reusing values may lead to duplicate key errors. ## AUTO\_INCREMENT On Reference Tables `AUTO_INCREMENT` values on reference tables are always strictly increasing. Following any manual inserts or updates to the `AUTO_INCREMENT` column in a reference table, subsequent automatically generated values are always greater than any existing value in the `AUTO_INCREMENT` column. Therefore, manually syncing the auto-increment values is not required on reference tables. ## Schema and Data Type Design * Use `AUTO_INCREMENT` columns as surrogate keys, and enforce business semantics in a separate column or a separate unique key. Use a separate column for the following: * Encoding semantic dimensions (e.g., tenant, region, type) * Multi-tenant or domain-scoped IDs * Use `BIGINT` (or `BIGINT UNSIGNED`) as the `AUTO_INCREMENT` column data type for high-volume tables. ## Semantics and Application Behavior * Because `AUTO_INCREMENT` values can have gaps due to rollbacks, failed inserts, etc., the sequence isn't contiguous. Design your application code to accept and accommodate this. * Avoid using `AUTO_INCREMENT` columns for ordering, especially with multi‑threaded ingestion or multi‑region architectures. If “most recent” or “first” semantics are required, order by a `DATETIME[(6)]` column or another dedicated ordering column, not by the `AUTO_INCREMENT` value. ## Capacity Planning and Monitoring Usage For high-volume ingest/update tables, plan `AUTO_INCREMENT` capacity based on expected ingest rates and identifier consumption over time. Monitor how quickly the values are consumed and ensure there is sufficient capacity in the identifier space. Use a larger data type if necessary. Optionally, create alerts (e.g. with a scheduled notebook or other scheduled job) for defined thresholds to plan schema or application changes, for example, 70% of maximum supported value of the column data type. *** Modified at: May 12, 2026 Source: [/db/v9.1/reference/sql-reference/data-definition-language-ddl/create-table-with-an-auto-increment-column/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-definition-language-ddl/create-table-with-an-auto-increment-column/) (An index of the documentation is available at /llms.txt)