---
title: "Indexes and Constraints"
description: "Indexes and Constraints represent a critical abstraction layer in Drizzle Kit that bridges the gap between database schema definitions in TypeScript and the concrete implementations in target RDBMS..."
last_updated: "2026-07-02T09:35:18.591974+00:00"
canonical_url: "https://www.doc0.app/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/technical/database-schema/indexes-and-constraints"
---

<details>
<summary>Relevant source files</summary>

The following files were used as context for generating this wiki page:

- [drizzle-kit/src/serializer/pgSerializer.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/serializer/pgSerializer.ts)
- [drizzle-kit/src/introspect-pg.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/introspect-pg.ts)
- [drizzle-kit/src/serializer/pgSchema.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/serializer/pgSchema.ts)
- [drizzle-kit/src/introspect-gel.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/introspect-gel.ts)
- [drizzle-kit/src/serializer/mysqlSerializer.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/serializer/mysqlSerializer.ts)
</details>

Indexes and Constraints represent a critical abstraction layer in Drizzle Kit that bridges the gap between database schema definitions in TypeScript and the concrete implementations in target RDBMS (PostgreSQL, MySQL, Gel). These structures ensure data integrity (constraints) and provide mechanisms for query performance optimization (indexes).

In the context of the Drizzle ecosystem, this subsystem serves as a serializer and introspector. During snapshot generation, it traverses the `AnyPgTable` objects (imported from `drizzle-orm/pg-core`), extracts metadata concerning constraints (primary keys, foreign keys, unique constraints, and check constraints) and index definitions, and transforms them into a unified, serializable JSON schema format. This transformation ensures that database changes can be accurately diffed and represented as migration operations.

The design relies on a clear separation between the schema *definitions* (the runtime TypeScript objects) and the *internals* or *snapshot representations* used during diffing. By standardizing these into standardized types defined in `pgSchema.ts`, Drizzle Kit can perform schema introspection against live databases and compare the results with the defined code, ensuring that index naming collisions or constraint inconsistencies are caught during development.

## Core Schema Structures and Types

The subsystem defines schemas using Zod for validation to ensure the integrity of the serialized snapshots. The schema structures act as the "source of truth" for the current state of a database, housing definitions for `indexes`, `foreignKeys`, `compositePrimaryKeys`, `uniqueConstraints`, and `checkConstraints`.

| Structure | Description | Key Properties |
| :--- | :--- | :--- |
| `Index` | Defines an index, potentially unique or covering multiple columns. | `columns`, `isUnique`, `method`, `where`, `concurrently` |
| `ForeignKey` | Represents a relational link between tables. | `tableFrom`, `columnsFrom`, `tableTo`, `onUpdate`, `onDelete` |
| `UniqueConstraint` | Ensures column data uniqueness. | `columns`, `nullsNotDistinct`, `name` |
| `CheckConstraint` | Validates data against a logical expression. | `name`, `value` |
| `PrimaryKey` | Identifies primary keys, including composite keys. | `columns`, `name` |

Sources: [drizzle-kit/src/serializer/pgSchema.ts:139-341](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/serializer/pgSchema.ts#L139-L341)

## Introspection Flow

Introspection is the process of querying the live database catalog and reconstructing the schema objects. This allows Drizzle to "see" the database state.

### Execution Walkthrough
1. **Catalog Querying**: The process starts by identifying existing tables via `pg_catalog.pg_class`.
2. **Table Constraint Collection**: For each table, the system queries `information_schema.table_constraints` to identify `PRIMARY KEY`, `UNIQUE`, and `CHECK` constraints.
3. **Foreign Key Mapping**: The system probes `pg_catalog.pg_constraint` to retrieve link metadata (which table references what, and associated rules like `ON DELETE` or `ON UPDATE`).
4. **Index Retrieval**: A query on `pg_index` and `pg_class` retrieves index metadata, including access methods (e.g., `btree`), expressions, and operator classes.
5. **Snapshot Construction**: These pieces are consolidated into the final schema object, which is then serialized.

Sources: [drizzle-kit/src/serializer/pgSerializer.ts:988-1664](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/serializer/pgSerializer.ts#L988-L1664), [drizzle-kit/src/introspect-pg.ts:1230-1652](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/introspect-pg.ts#L1230-L1652)

```mermaid
flowchart TD
    A["Query DB Catalog"] --> B["Fetch Constraints<br>via Information Schema"]
    B --> C["Fetch Foreign Key<br>Metadata"]
    C --> D["Fetch Index<br>Definitions"]
    D --> E["Reconstruct<br>Schema Object"]
    E --> F["Serialize to<br>Snapshot JSON"]
```
Sources: [drizzle-kit/src/introspect-pg.ts:1230-1652](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/introspect-pg.ts#L1230-L1652)

## Handling Constraints and Naming Collisions

A key mechanism within `generatePgSnapshot` is the detection of duplicated constraint names within a schema. This is handled by a local `checksInTable` or `indexesInSchema` lookup table.

> [!CAUTION]
> If a developer specifies a duplicate index name for different tables within the same schema, Drizzle Kit will detect this via the `indexesInSchema` lookup and terminate the process with an error, ensuring migration consistency.

The system uses `indexName()` to automatically generate names for indexes where the user has not provided one, preventing "unnamed" indexes from causing non-deterministic migrations.

Sources: [drizzle-kit/src/serializer/pgSerializer.ts:46-48](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/serializer/pgSerializer.ts#L46-L48), [drizzle-kit/src/serializer/pgSerializer.ts:124-129](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/serializer/pgSerializer.ts#L124-L129), [drizzle-kit/src/serializer/pgSerializer.ts:456-476](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/serializer/pgSerializer.ts#L456-L476)

## Index and Expression Handling

When dealing with expressions in indexes, the serializer requires the user to explicitly name the index, as automatic naming (which relies on column names) is insufficient.

```typescript
// Example: Index on an expression
index("my_index").using("btree", sql`lower(column_name)`)
```

The system validates the index configuration to ensure that specialized extensions (like `pg_vector`) have the necessary operator classes specified. The mechanism checks if the column type is a `PgVector` and iterates through a list of `vectorOps` to ensure the correct operator class is chosen.

Sources: [drizzle-kit/src/serializer/pgSerializer.ts:373-423](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/serializer/pgSerializer.ts#L373-L423)

## Architecture Comparison

| Design Choice | Benefit | Cost |
| :--- | :--- | :--- |
| **Snapshot-based Diffing** | Allows migration generation between any two states. | Requires maintaining state serialization. |
| **Centralized Serializer** | Uniform handling of index/constraint names. | High complexity in mapping dialect-specific behavior. |
| **Runtime Validation (Zod)** | Guarantees schema consistency in files. | Slight overhead during startup/execution. |

Sources: [drizzle-kit/src/serializer/pgSerializer.ts:101-113](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/serializer/pgSerializer.ts#L101-L113), [drizzle-kit/src/serializer/pgSchema.ts:5-46](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/serializer/pgSchema.ts#L5-L46)

## Schema to TypeScript Generation

The `schemaToTypeScript` function is responsible for converting the serialized snapshot back into TypeScript code. This involves iterating through the table definitions and invoking helper functions like `createTableIndexes` and `createTableFKs`.

The mechanism for creating indexes specifically maps the internal `Index` model to a string statement:

1. **Naming**: Resolves whether to use an explicit or generated index name.
2. **Method/Options**: Injects the index method (e.g., `btree`) and any specific `with` options.
3. **Columns**: Maps column objects to their Drizzle code counterparts, handling expressions, ordering, and operator classes.

Sources: [drizzle-kit/src/introspect-pg.ts:309-1201](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/introspect-pg.ts#L309-L1201)

```mermaid
sequenceDiagram
    participant S as Schema Object
    participant T as TypeScript Generator
    participant C as Column/Index/FK helpers
    
    S->>T: schemaToTypeScript(schema)
    loop For each table
        T->>C: createTableIndexes(table.indexes)
        C-->>T: returns index code strings
        T->>C: createTableFKs(table.foreignKeys)
        C-->>T: returns FK code strings
    end
    T-->>S: returns TypeScript file content
```
Sources: [drizzle-kit/src/introspect-pg.ts:309-565](https://github.com/blade47/drizzle-orm/blob/main/drizzle-kit/src/introspect-pg.ts#L309-L565)

## Related

- [Schema Declarations](https://www.doc0.app/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/technical/database-schema/schema-declarations)


## Sitemap

See the full [sitemap](https://www.doc0.app/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/llms.txt) for all pages in this wiki.