# Schema Migration

Schema migration (*v1.5.7*) is a powerful feature that allows Sling to replicate not just data, but also important schema attributes like primary keys, foreign keys, indexes, default values, nullable constraints, auto-increment columns, and column/table descriptions from source to target databases.

{% hint style="success" %}
**CLI Pro / Platform Feature**: Schema migration requires a [CLI Pro Max token](/sling-cli/cli-pro.md) or [Advanced Platform Plan](/sling-platform/platform.md).
{% endhint %}

{% hint style="info" %}
Schema migration is controlled via the `SLING_SCHEMA_MIGRATION` environment variable and is disabled by default.
{% endhint %}

## Overview

When migrating databases, preserving schema attributes is often critical for maintaining data integrity and application compatibility. Sling's schema migration feature extracts extended metadata from source tables and applies the corresponding constraints and properties to target tables.

### Supported Attributes

| Attribute        | Description                     | Example                               |
| ---------------- | ------------------------------- | ------------------------------------- |
| `primary_key`    | Primary key constraints         | `id INT PRIMARY KEY`                  |
| `foreign_key`    | Foreign key relationships       | `REFERENCES customers(id)`            |
| `indexes`        | Non-primary key indexes         | `CREATE INDEX idx_name ON table(col)` |
| `auto_increment` | Identity/auto-increment columns | `IDENTITY(1,1)` or `SERIAL`           |
| `nullable`       | NOT NULL constraints            | `email VARCHAR(255) NOT NULL`         |
| `default_value`  | Column default values           | `DEFAULT CURRENT_TIMESTAMP`           |
| `description`    | Column and table comments       | `COMMENT ON COLUMN...`                |

### Supported Databases

Schema migration works between any combination of these databases. The level of support varies by database—see the compatibility matrix below for details.

#### Full Support

* PostgreSQL
* MySQL / MariaDB
* SQL Server / Azure SQL
* Oracle
* Redshift
* Databricks

#### Partial Support

* Snowflake (no indexes)
* BigQuery (no auto-increment, no default values, no indexes)
* SQLite (no descriptions)
* DuckDB (no auto-increment, no default values)
* ClickHouse (primary key, nullable, description only)

### Database Compatibility Matrix

The following matrix shows which schema attributes are supported for each database:

| Database                   | Auto-Increment | Primary Key | Foreign Key | Default Value | Nullable | Indexes | Description |
| -------------------------- | :------------: | :---------: | :---------: | :-----------: | :------: | :-----: | :---------: |
| **PostgreSQL**             |        ✓       |      ✓      |      ✓      |       ✓       |     ✓    |    ✓    |      ✓      |
| **MySQL / MariaDB**        |        ✓       |      ✓      |      ✓¹     |       ✓       |     ✓    |    ✓    |      ✓      |
| **SQL Server / Azure SQL** |        ✓       |      ✓      |      ✓      |       ✓       |     ✓    |    ✓    |      ✓      |
| **Oracle**                 |        ✓       |      ✓      |      ✓²     |       ✓       |     ✓    |    ✓    |      ✓      |
| **Redshift**               |        ✓       |      ✓³     |      ✓³     |       ✓       |     ✓    |    —⁴   |      ✓      |
| **Databricks**             |       ✓⁵       |      ✓⁶     |      ✓⁶     |       ✓       |     ✓    |    —⁷   |      ✓      |
| **Snowflake**              |        ✓       |      ✓⁸     |      ✓⁸     |       ✓       |     ✓    |    —    |      ✓      |
| **BigQuery**               |        —       |      ✓⁹     |      ✓⁹     |       —       |     ✓    |    —    |      ✓      |
| **SQLite**                 |       ✓¹⁰      |      ✓      |     ✓¹¹     |       ✓       |     ✓    |    ✓    |      —      |
| **DuckDB**                 |        —       |      ✓      |      ✓      |       —       |     ✓    |    ✓    |      ✓      |
| **ClickHouse**             |        —       |     ✓¹²     |      —      |       ✓       |     ✓    |    —    |      ✓      |

**Legend:** ✓ = Supported | — = Not supported

**Notes:**

1. MySQL/MariaDB: Foreign keys require InnoDB engine; MyISAM ignores FK constraints
2. Oracle: Supports `ON DELETE` actions but not `ON UPDATE` actions for foreign keys
3. Redshift: Primary keys and foreign keys are **informational only** (not enforced)
4. Redshift: Does not support traditional indexes; use SORTKEY/DISTKEY instead
5. Databricks: Auto-increment only supports `BIGINT` column type
6. Databricks: Primary keys and foreign keys are **informational only** (not enforced); only NOT NULL is enforced
7. Databricks: Does not support traditional indexes; use Z-ordering instead
8. Snowflake: Primary keys and foreign keys are **not enforced** in standard tables (metadata only); only NOT NULL is enforced
9. BigQuery: Primary keys and foreign keys use `NOT ENFORCED` syntax (metadata only for query optimization)
10. SQLite: Auto-increment only works with `INTEGER PRIMARY KEY` columns
11. SQLite: Foreign keys require `PRAGMA foreign_keys = ON` per connection (disabled by default)
12. ClickHouse: Primary key defines sort order and sparse index; does not enforce uniqueness

### Constraint Enforcement Matrix

{% hint style="warning" %}
**Important:** Not all databases enforce constraints at runtime. Some databases accept constraint DDL for metadata/documentation purposes but do not validate data against those constraints. This matrix shows which constraints are actually enforced.
{% endhint %}

| Database            | PK Enforced |   FK Enforced  | NOT NULL Enforced | Default Enforced |
| ------------------- | :---------: | :------------: | :---------------: | :--------------: |
| **PostgreSQL**      |    ✓ Yes    |      ✓ Yes     |       ✓ Yes       |       ✓ Yes      |
| **MySQL / MariaDB** |    ✓ Yes    | ✓ Yes (InnoDB) |       ✓ Yes       |       ✓ Yes      |
| **SQL Server**      |    ✓ Yes    |      ✓ Yes     |       ✓ Yes       |       ✓ Yes      |
| **Oracle**          |    ✓ Yes    |      ✓ Yes     |       ✓ Yes       |       ✓ Yes      |
| **Redshift**        |     ✗ No    |      ✗ No      |       ✓ Yes       |       ✓ Yes      |
| **Databricks**      |     ✗ No    |      ✗ No      |       ✓ Yes       |       ✓ Yes      |
| **Snowflake**       |     ✗ No    |      ✗ No      |       ✓ Yes       |       ✓ Yes      |
| **BigQuery**        |     ✗ No    |      ✗ No      |       ✓ Yes       |      Partial     |
| **SQLite**          |    ✓ Yes    |     ✓ Yes\*    |       ✓ Yes       |       ✓ Yes      |
| **DuckDB**          |    ✓ Yes    |      ✓ Yes     |       ✓ Yes       |       ✓ Yes      |
| **ClickHouse**      |     ✗ No    |       N/A      |        ✗ No       |       ✓ Yes      |

**Legend:** ✓ Yes = Enforced at runtime | ✗ No = Metadata only (not enforced) | \* = Requires configuration

{% hint style="info" %}
**SQLite Note:** Foreign key enforcement requires `PRAGMA foreign_keys = ON` to be set on each database connection. This is disabled by default for backwards compatibility.
{% endhint %}

{% hint style="info" %}
**Analytical Databases:** Redshift, Snowflake, BigQuery, and Databricks are optimized for analytical workloads. They accept constraint definitions for documentation and query optimization hints, but do not enforce them at runtime. Data integrity must be validated upstream in ETL pipelines.
{% endhint %}

### What You Can Use for Each Database

{% tabs %}
{% tab title="PostgreSQL" %}
**All features supported and enforced.** PostgreSQL is fully compatible with schema migration.

```yaml
env:
  SLING_SCHEMA_MIGRATION: all
```

| Attribute      | Supported |  Enforced  |
| -------------- | :-------: | :--------: |
| Primary Key    |     ✓     |    ✓ Yes   |
| Foreign Key    |     ✓     |    ✓ Yes   |
| Auto-increment |     ✓     |    ✓ Yes   |
| Default Value  |     ✓     |    ✓ Yes   |
| NOT NULL       |     ✓     |    ✓ Yes   |
| Indexes        |     ✓     | Functional |
| Description    |     ✓     |  Metadata  |

* Auto-increment uses `GENERATED BY DEFAULT AS IDENTITY`
* Full foreign key support with `ON DELETE` and `ON UPDATE` actions
* Column/table comments via `COMMENT ON` syntax
  {% endtab %}

{% tab title="MySQL / MariaDB" %}
**All features supported and enforced** (with InnoDB engine).

```yaml
env:
  SLING_SCHEMA_MIGRATION: all
```

| Attribute      | Supported |       Enforced      |
| -------------- | :-------: | :-----------------: |
| Primary Key    |     ✓     |        ✓ Yes        |
| Foreign Key    |     ✓     | ✓ Yes (InnoDB only) |
| Auto-increment |     ✓     |        ✓ Yes        |
| Default Value  |     ✓     |        ✓ Yes        |
| NOT NULL       |     ✓     |        ✓ Yes        |
| Indexes        |     ✓     |      Functional     |
| Description    |     ✓     |       Metadata      |

* Auto-increment uses `AUTO_INCREMENT` keyword
* Foreign keys require **InnoDB** engine (MyISAM ignores FK constraints)
* Comments via `COMMENT` clause
  {% endtab %}

{% tab title="SQL Server" %}
**All features supported and enforced.** SQL Server and Azure SQL are fully compatible.

```yaml
env:
  SLING_SCHEMA_MIGRATION: all
```

| Attribute      | Supported |  Enforced  |
| -------------- | :-------: | :--------: |
| Primary Key    |     ✓     |    ✓ Yes   |
| Foreign Key    |     ✓     |    ✓ Yes   |
| Auto-increment |     ✓     |    ✓ Yes   |
| Default Value  |     ✓     |    ✓ Yes   |
| NOT NULL       |     ✓     |    ✓ Yes   |
| Indexes        |     ✓     | Functional |
| Description    |     ✓     |  Metadata  |

* Auto-increment uses `IDENTITY(seed, increment)` syntax
* Foreign keys can be disabled with `NOCHECK` if needed
* Descriptions via Extended Properties (`MS_Description`)
  {% endtab %}

{% tab title="Oracle" %}
**All features supported and enforced** with one limitation.

```yaml
env:
  SLING_SCHEMA_MIGRATION: all
```

| Attribute      | Supported |        Enforced        |
| -------------- | :-------: | :--------------------: |
| Primary Key    |     ✓     |          ✓ Yes         |
| Foreign Key    |     ✓     | ✓ Yes (ON DELETE only) |
| Auto-increment |     ✓     |          ✓ Yes         |
| Default Value  |     ✓     |          ✓ Yes         |
| NOT NULL       |     ✓     |          ✓ Yes         |
| Indexes        |     ✓     |       Functional       |
| Description    |     ✓     |        Metadata        |

* Auto-increment uses `GENERATED BY DEFAULT AS IDENTITY`
* Foreign keys support `ON DELETE` actions but **not `ON UPDATE`** (Oracle limitation)
* Comments via `COMMENT ON COLUMN` / `COMMENT ON TABLE`
  {% endtab %}

{% tab title="Snowflake" %}
**DDL supported but constraints not enforced.** Snowflake is an analytical database.

```yaml
env:
  SLING_SCHEMA_MIGRATION: primary_key,foreign_key,auto_increment,nullable,default_value,description
```

| Attribute      | Supported |       Enforced       |
| -------------- | :-------: | :------------------: |
| Primary Key    |     ✓     | ✗ No (metadata only) |
| Foreign Key    |     ✓     | ✗ No (metadata only) |
| Auto-increment |     ✓     |         ✓ Yes        |
| Default Value  |     ✓     |         ✓ Yes        |
| NOT NULL       |     ✓     |         ✓ Yes        |
| Indexes        |     —     |          N/A         |
| Description    |     ✓     |       Metadata       |

* Auto-increment uses `AUTOINCREMENT START ... INCREMENT ...`
* **Primary keys and foreign keys are not enforced** — they exist for documentation and query optimization only
* Only `NOT NULL` constraints are enforced in standard tables
* Indexes are not applicable (Snowflake uses clustering keys instead)
  {% endtab %}

{% tab title="BigQuery" %}
**Limited support.** BigQuery constraints are metadata only.

```yaml
env:
  SLING_SCHEMA_MIGRATION: primary_key,foreign_key,nullable,description
```

| Attribute      | Supported |       Enforced      |
| -------------- | :-------: | :-----------------: |
| Primary Key    |     ✓     | ✗ No (NOT ENFORCED) |
| Foreign Key    |     ✓     | ✗ No (NOT ENFORCED) |
| Auto-increment |     —     |         N/A         |
| Default Value  |     —     |         N/A         |
| NOT NULL       |     ✓     |        ✓ Yes        |
| Indexes        |     —     |         N/A         |
| Description    |     ✓     |       Metadata      |

* Primary keys and foreign keys use `NOT ENFORCED` syntax (query optimization hints only)
* Column descriptions via `ALTER TABLE ... SET OPTIONS`
* No identity columns—use application-generated IDs or `GENERATE_UUID()`
  {% endtab %}

{% tab title="Redshift" %}
**DDL supported but PK/FK not enforced.** Redshift is an analytical data warehouse.

```yaml
env:
  SLING_SCHEMA_MIGRATION: primary_key,foreign_key,auto_increment,nullable,default_value,description
```

| Attribute      | Supported |       Enforced       |
| -------------- | :-------: | :------------------: |
| Primary Key    |     ✓     | ✗ No (informational) |
| Foreign Key    |     ✓     | ✗ No (informational) |
| Auto-increment |     ✓     |         ✓ Yes        |
| Default Value  |     ✓     |         ✓ Yes        |
| NOT NULL       |     ✓     |         ✓ Yes        |
| Indexes        |     —     |          N/A         |
| Description    |     ✓     |       Metadata       |

* Auto-increment uses `IDENTITY(seed, increment)`
* **Primary keys and foreign keys are informational only** — used as query optimizer hints, not enforced
* Does not support traditional indexes; use `SORTKEY` and `DISTKEY` for performance
* Comments via `COMMENT ON` syntax
  {% endtab %}

{% tab title="SQLite" %}
**Most features supported.** Foreign keys require explicit enabling.

```yaml
env:
  SLING_SCHEMA_MIGRATION: primary_key,foreign_key,auto_increment,nullable,default_value,indexes
```

| Attribute      | Supported |       Enforced       |
| -------------- | :-------: | :------------------: |
| Primary Key    |     ✓     |         ✓ Yes        |
| Foreign Key    |     ✓     | ✓ Yes (when enabled) |
| Auto-increment |     ✓     |         ✓ Yes        |
| Default Value  |     ✓     |         ✓ Yes        |
| NOT NULL       |     ✓     |         ✓ Yes        |
| Indexes        |     ✓     |      Functional      |
| Description    |     —     |          N/A         |

* Auto-increment only works with `INTEGER PRIMARY KEY` columns
* **Foreign keys require `PRAGMA foreign_keys = ON`** per connection (disabled by default)
* No native column/table comment support
  {% endtab %}

{% tab title="DuckDB" %}
**Partial support but constraints are enforced.** DuckDB enforces integrity unlike most analytical DBs.

```yaml
env:
  SLING_SCHEMA_MIGRATION: primary_key,foreign_key,nullable,indexes,description
```

| Attribute      | Supported |  Enforced  |
| -------------- | :-------: | :--------: |
| Primary Key    |     ✓     |    ✓ Yes   |
| Foreign Key    |     ✓     |    ✓ Yes   |
| Auto-increment |     —     |     N/A    |
| Default Value  |     —     |     N/A    |
| NOT NULL       |     ✓     |    ✓ Yes   |
| Indexes        |     ✓     | Functional |
| Description    |     ✓     |  Metadata  |

* No native identity columns—use sequences with `DEFAULT nextval('seq')`
* **Foreign keys are enforced** (unlike Snowflake, BigQuery, Redshift)
* Indexes are fully supported via `duckdb_indexes()` metadata
* Column descriptions via `COMMENT ON` syntax
  {% endtab %}

{% tab title="Databricks" %}
**DDL supported but PK/FK not enforced.** Databricks uses Delta Lake.

```yaml
env:
  SLING_SCHEMA_MIGRATION: primary_key,foreign_key,auto_increment,nullable,default_value,description
```

| Attribute      | Supported |       Enforced       |
| -------------- | :-------: | :------------------: |
| Primary Key    |     ✓     | ✗ No (informational) |
| Foreign Key    |     ✓     | ✗ No (informational) |
| Auto-increment |     ✓     |         ✓ Yes        |
| Default Value  |     ✓     |         ✓ Yes        |
| NOT NULL       |     ✓     |         ✓ Yes        |
| Indexes        |     —     |          N/A         |
| Description    |     ✓     |       Metadata       |

* Auto-increment uses `GENERATED BY DEFAULT AS IDENTITY` (**BIGINT only**)
* **Note:** When Databricks is used as a *source*, identity column detection is limited (Databricks doesn't expose identity metadata via `information_schema`)
* **Primary keys and foreign keys are informational only** — used for query optimization
* Only `NOT NULL` and `CHECK` constraints are enforced
* Does not support traditional indexes; use Z-ordering for performance
  {% endtab %}

{% tab title="ClickHouse" %}
**Limited support.** ClickHouse is optimized for OLAP, not constraint enforcement.

```yaml
env:
  SLING_SCHEMA_MIGRATION: primary_key,nullable,default_value,description
```

| Attribute      | Supported |        Enforced        |
| -------------- | :-------: | :--------------------: |
| Primary Key    |     ✓     | ✗ No (sort order only) |
| Foreign Key    |     —     |           N/A          |
| Auto-increment |     —     |           N/A          |
| Default Value  |     ✓     |          ✓ Yes         |
| NOT NULL       |     ✓     |          ✗ No          |
| Indexes        |     —     |           N/A          |
| Description    |     ✓     |        Metadata        |

* **Primary key defines sort order and sparse index** — does not enforce uniqueness
* No foreign key support
* No auto-increment columns (use `generateSerialID()` in v25.1+)
* NOT NULL is not enforced; use `Nullable(T)` wrapper to allow NULLs
* Uses data-skipping indexes (MinMax, Bloom) instead of traditional indexes
  {% endtab %}
  {% endtabs %}

## Enabling Schema Migration

Set the `SLING_SCHEMA_MIGRATION` environment variable to enable specific attributes:

```yaml
env:
  # Enable all schema attributes
  SLING_SCHEMA_MIGRATION: all

  # Or enable specific attributes (comma-separated)
  SLING_SCHEMA_MIGRATION: primary_key,foreign_key,indexes
```

### Available Options

| Value            | Description                             |
| ---------------- | --------------------------------------- |
| `all`            | Enable all schema migration attributes  |
| `primary_key`    | Migrate primary key constraints         |
| `foreign_key`    | Migrate foreign key relationships       |
| `indexes`        | Migrate indexes (non-PK)                |
| `auto_increment` | Migrate identity/auto-increment columns |
| `nullable`       | Migrate NOT NULL constraints            |
| `default_value`  | Migrate column default values           |
| `description`    | Migrate column and table comments       |

## Basic Example

Migrate all schema attributes from SQL Server to PostgreSQL:

```yaml
source: mssql
target: postgres

defaults:
  mode: full-refresh
  object: public.{stream_table}

streams:
  dbo.customers:
  dbo.orders:
  dbo.products:

env:
  SLING_SCHEMA_MIGRATION: all
```

## Foreign Key Migration

When migrating foreign keys, Sling automatically handles table ordering to ensure parent tables are created before child tables that reference them.

{% hint style="warning" %}
All referenced tables must be included in the replication. If table `orders` has a foreign key to `customers`, both tables must be in the streams list.
{% endhint %}

### Automatic Table Ordering

Sling performs topological sorting based on foreign key dependencies. Even if you list tables in the wrong order, they will be processed correctly:

```yaml
source: mssql
target: postgres

defaults:
  mode: full-refresh

# Tables listed in wrong order - Sling will reorder them automatically
# order_items -> orders -> customers (FK dependencies)
streams:
  dbo.order_items:
    object: public.order_items

  dbo.orders:
    object: public.orders

  dbo.customers:  # Will be processed first due to FK dependencies
    object: public.customers

env:
  SLING_SCHEMA_MIGRATION: foreign_key,primary_key
  SLING_THREADS: 3  # works with threads!
```

Sling will automatically reorder streams to: `customers` → `orders` → `order_items`

### Handling Circular Dependencies

If circular foreign key dependencies are detected, Sling will report an error. Options to resolve:

1. Remove one table from the replication
2. Disable foreign key migration
3. Handle FK creation manually via hooks

## Auto-Increment / Identity Columns

Sling migrates auto-increment columns with their seed and increment values:

```yaml
source: mssql     # IDENTITY(1000, 10)
target: postgres  # GENERATED BY DEFAULT AS IDENTITY (START WITH 1000 INCREMENT BY 10)

streams:
  dbo.invoices:
    object: public.invoices

env:
  SLING_SCHEMA_MIGRATION: auto_increment,primary_key
```

### Database-Specific Behavior

| Source                         | Target     | Result                             |
| ------------------------------ | ---------- | ---------------------------------- |
| SQL Server `IDENTITY(1,1)`     | PostgreSQL | `GENERATED BY DEFAULT AS IDENTITY` |
| PostgreSQL `SERIAL`            | MySQL      | `AUTO_INCREMENT`                   |
| Oracle `GENERATED AS IDENTITY` | SQL Server | `IDENTITY(seed, incr)`             |

## Default Values

Sling translates default value expressions between databases:

```yaml
source: mssql
target: postgres

streams:
  dbo.audit_logs:

env:
  SLING_SCHEMA_MIGRATION: default_value
```

### Default Value Translation

| Expression        | SQL Server     | PostgreSQL                             | MySQL               | Oracle                          |
| ----------------- | -------------- | -------------------------------------- | ------------------- | ------------------------------- |
| Current timestamp | `GETDATE()`    | `CURRENT_TIMESTAMP`                    | `CURRENT_TIMESTAMP` | `SYSDATE`                       |
| UTC timestamp     | `GETUTCDATE()` | `CURRENT_TIMESTAMP AT TIME ZONE 'UTC'` | `UTC_TIMESTAMP()`   | `SYS_EXTRACT_UTC(SYSTIMESTAMP)` |
| UUID              | `NEWID()`      | `gen_random_uuid()`                    | `UUID()`            | `SYS_GUID()`                    |
| Boolean true      | `1`            | `true`                                 | `1`                 | `1`                             |
| Boolean false     | `0`            | `false`                                | `0`                 | `0`                             |

## Column & Table Descriptions

Migrate comments and descriptions to preserve documentation:

```yaml
source: mssql
target: postgres

streams:
  dbo.products:

env:
  SLING_SCHEMA_MIGRATION: description
```

This will migrate:

* Column comments (e.g., `COMMENT ON COLUMN products.price IS 'Product retail price'`)
* Table comments (e.g., `COMMENT ON TABLE products IS 'Product catalog'`)

## Indexes

Migrate non-primary key indexes to preserve query performance:

```yaml
source: mssql
target: postgres

streams:
  dbo.customers:

env:
  SLING_SCHEMA_MIGRATION: indexes
```

This migrates regular indexes, unique indexes, and composite indexes.

{% hint style="info" %}
Index names are automatically generated in the target database to avoid conflicts. The format is `idx_{table}_{columns}`.
{% endhint %}

## Complete E-Commerce Migration Example

A comprehensive example migrating an e-commerce schema with all relationships:

```yaml
source: mssql
target: postgres

defaults:
  mode: full-refresh
  object: public.{stream_table}

streams:
  # Parent tables (no FK dependencies)
  dbo.categories:
  dbo.customers:

  # Child tables (have FK to parent tables)
  dbo.products:     # FK to categories
  dbo.orders:       # FK to customers

  # Grandchild tables (have FK to child tables)
  dbo.order_items:  # FK to orders and products

env:
  SLING_SCHEMA_MIGRATION: all
```

## Selective Attribute Migration

Enable only specific attributes based on your needs:

### Data Integrity Focus

```yaml
env:
  # Focus on constraints that ensure data integrity
  SLING_SCHEMA_MIGRATION: primary_key,foreign_key,nullable
```

### Performance Focus

```yaml
env:
  # Focus on attributes that affect query performance
  SLING_SCHEMA_MIGRATION: primary_key,indexes
```

### Documentation Preservation

```yaml
env:
  # Preserve schema documentation only
  SLING_SCHEMA_MIGRATION: description
```

## Using with Pipelines

Schema migration can also be used within pipelines:

```yaml
steps:
  # Setup source schema
  - connection: mssql
    query: |
      CREATE TABLE dbo.users (
        id INT IDENTITY(1,1) PRIMARY KEY,
        email NVARCHAR(255) NOT NULL,
        created_at DATETIME DEFAULT GETDATE()
      );

  # Run replication with schema migration
  - replication:
      source: mssql
      target: postgres

      streams:
        dbo.users:
          object: public.users

      env:
        SLING_SCHEMA_MIGRATION: all

  # Verify constraints were created
  - connection: postgres
    query: |
      SELECT constraint_type, constraint_name
      FROM information_schema.table_constraints
      WHERE table_name = 'users'
```

## Incremental Mode Considerations

Schema migration attributes are applied during table creation. When using `incremental` mode:

* **First run**: Table is created with all schema attributes
* **Subsequent runs**: Only data is upserted; schema is not modified

```yaml
source: mssql
target: postgres

defaults:
  mode: incremental
  primary_key: id
  update_key: updated_at

streams:
  dbo.orders:
    object: public.orders

env:
  SLING_SCHEMA_MIGRATION: all
```

## Troubleshooting

### Missing Foreign Key Dependencies

**Error**: `missing foreign key dependencies: 'orders' depends on 'customers' (not in stream list)`

**Solution**: Add the missing referenced table to your streams:

```yaml
streams:
  dbo.customers:  # Add the referenced table
  dbo.orders:
```

### Circular Dependency Detected

**Error**: `circular foreign key dependency detected involving: table_a, table_b`

**Solution**: Either disable FK migration or handle FKs manually with hooks:

```yaml
hooks:
  end:
    - connection: postgres
      query: |
        ALTER TABLE table_a ADD CONSTRAINT fk_a_b
        FOREIGN KEY (b_id) REFERENCES table_b(id);
```

### Unsupported Default Expression

If a default expression cannot be translated, Sling will pass it through as-is with a debug warning. You may need to manually adjust the default in the target database.

### Identity Column Conflicts

When using `auto_increment` migration, Sling uses `GENERATED BY DEFAULT AS IDENTITY` (PostgreSQL/Oracle) to allow explicit value inserts during data migration while still supporting auto-generation for new inserts.

## Best Practices

1. **Test First**: Run schema migration on a test environment before production
2. **Start Selective**: Begin with `primary_key` only, then add more attributes
3. **Include All Dependencies**: Ensure all FK-referenced tables are in the replication
4. **Use Full-Refresh**: Schema attributes are best applied with `full-refresh` mode initially
5. **Review Generated Schema**: After migration, review the target schema for any needed adjustments
6. **Consider Indexes Separately**: Large tables may benefit from creating indexes after data load via hooks

## See Also

* [Replication Modes](/concepts/replication/modes.md)
* [Hooks](https://github.com/slingdata-io/sling-docs/blob/master/concepts/hooks/hooks.md)
* [Target Options](/concepts/replication/target-options.md)


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.slingdata.io/examples/database-to-database/schema-migration.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.