# Indexes & Constraints

Beyond fields, a table can declare a primary key, indexes for query performance and uniqueness, unique-constraint sugar, CHECK constraints for cross-field rules, and composite foreign keys.

## Primary Key

Set `primaryKey` to control how each row is uniquely identified. When omitted, an auto-generated `id` column is the primary key.

| Property | Description                                                                                                            |
| -------- | ---------------------------------------------------------------------------------------------------------------------- |
| `type`   | Generation strategy: `auto-increment` (sequential integers), `uuid` (random unique ids), or `composite` (multi-field). |
| `field`  | Field name for a single-column key. Only used with `auto-increment` or `uuid`. Must match `^[a-z][a-z0-9_]*`.          |
| `fields` | Array of field names for a composite primary key.                                                                      |

```yaml
primaryKey: { type: auto-increment, field: id }
```

```yaml
# Composite primary key
primaryKey: { type: composite, fields: [tenant_id, slug] }
```

## Indexes

`indexes` is an array of index definitions. Index names must be unique within the table.

| Property | Description                                                                                                          |
| -------- | -------------------------------------------------------------------------------------------------------------------- |
| `name`   | Index name. Must match `^[a-z][a-z0-9_]*`. Use descriptive names like `idx_users_email`.                             |
| `fields` | Array of field names covered by the index. At least one required.                                                    |
| `unique` | Boolean. When `true`, enforces uniqueness across the indexed fields.                                                 |
| `where`  | SQL `WHERE` clause for a **partial index** — indexes only rows satisfying the condition (e.g. `deleted_at IS NULL`). |

```yaml
indexes:
  - { name: idx_users_email, fields: [email], unique: true }
  - { name: idx_orders_status, fields: [status] }
  # Partial unique index — uniqueness only on non-deleted rows
  - { name: idx_active_slug, fields: [slug], unique: true, where: 'deleted_at IS NULL' }
```

## Unique Constraints (top-level `unique`)

The table-level `unique` array is sugar for declaring uniqueness over one or more fields. Single-field entries fold into the equivalent of `field.unique = true`; multi-field entries become a unique btree index.

```yaml
unique:
  - { fields: [slug] } # single-field uniqueness
  - { fields: [tenant_id, slug] } # composite uniqueness
```

## CHECK Constraints

`constraints` enforces complex business rules at the database level with SQL boolean expressions. Constraint names must be unique within the table and match `^[a-z][a-z0-9_]*`.

| Property | Description                                                              |
| -------- | ------------------------------------------------------------------------ |
| `name`   | Unique constraint name (lowercase, alphanumeric, underscores).           |
| `check`  | PostgreSQL boolean expression that must evaluate to TRUE for valid data. |

```yaml
constraints:
  - { name: chk_price_positive, check: 'price > 0' }
  - { name: chk_end_after_start, check: 'end_date > start_date' }
  - { name: chk_active_members_have_email, check: '(is_active = false) OR (email IS NOT NULL)' }
```

## Composite Foreign Keys

Single-column foreign keys are created automatically from [`relationship`](/en/docs/relational-fields) fields. Use `foreignKeys` for **multi-column** references to a composite primary key in another table.

| Property           | Description                                                                      |
| ------------------ | -------------------------------------------------------------------------------- |
| `name`             | Constraint name (lowercase, underscores, max 63 chars).                          |
| `fields`           | Local columns forming the foreign key.                                           |
| `referencedTable`  | Parent table containing the referenced columns.                                  |
| `referencedFields` | Columns in the parent table that are referenced.                                 |
| `onDelete`         | Referential action on delete: `cascade`, `set-null`, `restrict`, or `no-action`. |
| `onUpdate`         | Referential action on update: `cascade`, `set-null`, `restrict`, or `no-action`. |

```yaml
foreignKeys:
  - name: fk_permissions_tenant_user
    fields: [tenant_id, user_id]
    referencedTable: tenant_users
    referencedFields: [tenant_id, user_id]
    onDelete: cascade
    onUpdate: cascade
```

:::callout
**Indexes vs constraints.** Use `indexes` for query performance and single/multi-field uniqueness; use `constraints` (CHECK) for conditional and cross-field validation that uniqueness cannot express. Both are validated against the table's field names at config time.
:::