Auto-generated Types

Handle issues with auto-generated database types and sequences.

Overview

Auto-generated types include:

  • Identity columns: SQL Server IDENTITY

  • Auto-increment: MySQL AUTO_INCREMENT

  • Sequences: PostgreSQL, Oracle sequences

  • Generated columns: Computed columns

Identity Columns (SQL Server)

Issue: Cannot Insert into IDENTITY Column

Symptom: "Cannot insert explicit value for identity column".

Solution: TDK handles identity columns automatically. Ensure your workflow doesn’t explicitly set identity values:

# ✓ Correct: Let TDK handle identity
transformations:
  - columns: ["name", "email"]
    params:
      type: person_generator
      column_templates: ["${first_name}", "${email}"]

# ❌ Wrong: Don't transform identity columns
transformations:
  - columns: ["id", "name"]  # id is IDENTITY
    params:
      type: int_sequence_generator  # Don't do this for IDENTITY columns!

AUTO_INCREMENT (MySQL)

Issue: Duplicate Key Errors

Symptom: "Duplicate entry for key 'PRIMARY'".

Solution: Reset AUTO_INCREMENT before generation:

ALTER TABLE customers AUTO_INCREMENT = 1;

Or use TDK’s sequence transformer:

transformations:
  - columns: ["id"]
    params:
      type: int_sequence_generator
      start_from: 1

Sequences (PostgreSQL, Oracle)

Issue: Sequence Out of Sync

Symptom: "Duplicate key value violates unique constraint".

Cause: Sequence current value doesn’t match max ID in table.

Solution:

PostgreSQL:

-- Reset sequence to max value
SELECT setval('customers_id_seq', (SELECT MAX(id) FROM customers));

Oracle:

-- Drop and recreate sequence
DROP SEQUENCE customers_seq;
CREATE SEQUENCE customers_seq START WITH 10000;

Generated/Computed Columns

Issue: Cannot Insert into Computed Column

Symptom: "Cannot insert into a generated column".

Solution: Exclude generated columns from transformations:

table_schema:
  - table_name_pattern: "public.customers"
    transformations:
      - columns: ["first_name", "last_name"]
        params:
          type: person_generator
          column_templates: ["${first_name}", "${last_name}"]
      # Don't transform 'full_name' if it's computed

Check for generated columns:

-- PostgreSQL
SELECT column_name, is_generated
FROM information_schema.columns
WHERE table_name = 'customers' AND is_generated = 'ALWAYS';

-- SQL Server
SELECT name, is_computed
FROM sys.columns
WHERE object_id = OBJECT_ID('customers') AND is_computed = 1;

See Also