# 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](development-guide.md#choosing-identifier-types) in the development guide for detailed guidance.

**ALWAYS use `GENERATED AS IDENTITY`** for auto-incrementing integer columns.

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

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

```sql
transact([
  "CREATE TABLE users (
     id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
     name VARCHAR(255)
   )"
])
```

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

Use `GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY` when compact, human-readable integer IDs are needed. CACHE **MUST** be specified explicitly as either `1` or `>= 65536`.

```sql
-- GENERATED ALWAYS: DSQL always generates the value; explicit inserts rejected unless OVERRIDING SYSTEM VALUE
transact([
  "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)
transact([
  "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)
transact(["CREATE SEQUENCE users_id_seq CACHE 65536 START 1"])

-- Create table using the sequence
transact([
  "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
transact([
  "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)
   )"
])

transact([
  "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
transact([
  "CREATE TABLE users_new (
     id BIGINT GENERATED BY DEFAULT AS IDENTITY (CACHE 65536) PRIMARY KEY,
     name VARCHAR(255)
   )"
])

-- Migrate with original integer IDs preserved
transact([
  "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:
readonly_query("SELECT MAX(id) as max_id FROM users_new")
-- Then reset the sequence (replace 'users_new_id_seq' with actual sequence name from get_schema):
transact(["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))