# MySQL to DSQL: AUTO_INCREMENT Migration

Part of [MySQL to DSQL DDL Migration](ddl-operations.md). See [Common Verify & Swap Pattern](ddl-operations.md#common-verify--swap-pattern) for the shared migration end-pattern.

---

## AUTO_INCREMENT Migration

**MySQL syntax:**

```sql
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255)
);
```

DSQL provides three options for replacing MySQL's AUTO_INCREMENT. Choose based on your workload requirements. See [Choosing Identifier Types](../auth/scaling-guide.md#choosing-identifier-types) in the scaling guide for detailed guidance.

**When choosing integer auto-increment, ALWAYS use `GENERATED AS IDENTITY`** (not `SERIAL`, which DSQL does not support). UUIDs (Option 1) remain the recommended default.

### Option 1: UUID Primary Key (Recommended for Scalability)

UUIDs are the recommended default because they avoid coordination and scale well for distributed writes.

```sql
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255)
);
```

> **DSQL: `gen_random_uuid()` is built-in; do NOT run `CREATE EXTENSION pgcrypto`.** DSQL ships
> PostgreSQL 16's core `gen_random_uuid()`, so the extension is unnecessary AND `CREATE EXTENSION`
> is rejected by DSQL (`ERROR: unsupported statement: CreateExtension`). Other `pgcrypto`
> functions (`crypt()`, `digest()`, `hmac()`, etc.) are unavailable — implement those at the
> application layer.

### Option 2: IDENTITY Column (Recommended for Integer Auto-Increment)

Use `GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY` when compact, human-readable integer IDs
are needed.

> **DSQL: `CACHE` is mandatory.** DSQL has **no implicit default** and rejects identity columns
> declared without it: `ERROR: identity column is not supported without an explicit cache size.
> please define CACHE greater than or equal to 65536 or equal to 1`. A migration tool replaying
> a vanilla PostgreSQL dump (where `CACHE` defaults to 1) will fail at the first `IDENTITY`
> column. Always include `(CACHE 1)` for strict ordering or `(CACHE 65536)` (or higher) for
> high-throughput workloads — see [scaling-guide.md](../auth/scaling-guide.md#choosing-identifier-types).

```sql
-- GENERATED ALWAYS: DSQL always generates the value; explicit inserts rejected unless OVERRIDING SYSTEM VALUE
CREATE TABLE users (
  id BIGINT GENERATED ALWAYS AS IDENTITY (CACHE 65536) PRIMARY KEY,
  name VARCHAR(255)
);

-- GENERATED BY DEFAULT: DSQL generates a value unless an explicit value is provided (closer to MySQL AUTO_INCREMENT behavior)
CREATE TABLE users (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY (CACHE 65536) PRIMARY KEY,
  name VARCHAR(255)
);
```

#### Choosing a CACHE Size

**REQUIRED:** Specify CACHE explicitly. Supported values are `1` or `>= 65536`.

- **CACHE >= 65536** — High-frequency inserts, many concurrent sessions, tolerates gaps and ordering effects (e.g., IoT/telemetry, job IDs, order numbers)
- **CACHE = 1** — Low allocation rates, identifiers should follow allocation order closely, minimizing gaps matters more than throughput (e.g., account numbers, reference numbers)

### Option 3: Explicit SEQUENCE

Use a standalone sequence when multiple tables share a counter or when you need `nextval`/`setval` control.

```sql
-- Create the sequence (CACHE MUST be 1 or >= 65536)
CREATE SEQUENCE users_id_seq CACHE 65536 START 1;

-- Create table using the sequence
CREATE TABLE users (
  id BIGINT PRIMARY KEY DEFAULT nextval('users_id_seq'),
  name VARCHAR(255)
);
```

### Migrating Existing AUTO_INCREMENT Data

#### To UUID Primary Key

```sql
CREATE TABLE users_new (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  legacy_id INTEGER,  -- Preserve original AUTO_INCREMENT ID for reference
  name VARCHAR(255)
);

INSERT INTO users_new (id, legacy_id, name)
SELECT gen_random_uuid(), id, name
FROM users;
```

If other tables reference the old integer ID, update those references to use the new UUID or the `legacy_id` column.

#### To IDENTITY Column (Preserving Integer IDs)

```sql
-- Use GENERATED BY DEFAULT to allow explicit ID values during migration
CREATE TABLE users_new (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY (CACHE 65536) PRIMARY KEY,
  name VARCHAR(255)
);

-- Migrate with original integer IDs preserved
INSERT INTO users_new (id, name)
SELECT id, name
FROM users;

-- Set the identity sequence to continue after the max existing ID
-- Get the max ID first:
SELECT MAX(id) as max_id FROM users_new;
-- Then reset the sequence (find the sequence name via:
--   SELECT pg_get_serial_sequence('users_new', 'id');):
SELECT setval('users_new_id_seq', (SELECT MAX(id) FROM users_new));
```

**Verify and swap** (see [Common Pattern](ddl-operations.md#common-verify--swap-pattern))