drizzle-team / drizzle-team/drizzle-orm

Feature request: support `ON DELETE SET NULL (column_list)` for composite foreign keys (Postgres 15+)

Open
#5,684 1 comment 7 reactions 0 assignees View on GitHub
Dominant language
TypeScript
Stars
35.8k
Forks
1.6k
Avg merge
2d 7h
Merged PRs (30d)
4

Description

## What

Postgres 15 added the ability to null a subset of columns when a multi-column foreign key fires `ON DELETE SET NULL` (or `SET DEFAULT`):

```sql
FOREIGN KEY (a, b) REFERENCES other(x, y) ON DELETE SET NULL (b)
```

Reference: [Postgres 15 ALTER TABLE](https://www.postgresql.org/docs/15/sql-altertable.html), [CREATE TABLE](https://www.postgresql.org/docs/15/sql-createtable.html).

Drizzle ORM does not currently model this. `UpdateDeleteAction` in `src/pg-core/foreign-keys.ts` only allows the action keyword:

```ts
type UpdateDeleteAction =
| "cascade"
| "restrict"
| "no action"
| "set null"
| "set default";
```

There's no way to pass a column subset.

## Why it matters

The plain `ON DELETE SET NULL` action nulls **every** column in the FK. For composite FKs whose other columns are part of the primary key or are generated columns, plain `SET NULL` is unusable:

```ts
// Generated column. Postgres rejects any UPDATE that targets a generated column, so a
// plain ON DELETE SET NULL on the composite FK fails the moment the parent row is deleted.
selectedCoverKind: imageKindEnum().generatedAlwaysAs(sql`'cover'::image_kind`),
selectedCoverImageId: text(),
// FK (selectedCoverKind, selectedCoverImageId) -> images(kind, id)

// Primary-key column
campaignId: text().notNull(),
entityKind: imageKindEnum().notNull(),
entityName: text().notNull(),
imageId: text(),
// PK (campaignId, entityKind, entityName)
// FK (entityKind, imageId) -> images(kind, id)
```

In both cases, only the nullable column should be nulled on delete, because the partner column is structurally non-nullable. Postgres 15+ supports exactly this. Drizzle has no surface for it.

## Today's options for users

- `'no action'` / `'restrict'`: image delete fails while a reference exists; app must clear the reference first.
- `'cascade'`: deletes the entire row, which often loses unrelated state (e.g. the selection's `(campaignId, entityKind, entityName)` anchor).
- Hand-edit the generated migration SQL to add the `(column_list)` clause. The snapshot doesn't track the column list, so the next `db:generate` won't re-emit a change. The hand-edit is still invisible to Drizzle and easy to lose on subsequent regenerations of the same FK.

None of these match Postgres 15+'s native behavior. We'd like to declare the column subset in the schema and have Drizzle emit and snapshot it.

## Proposed surface

```ts
foreignKey({
columns: [t.entityKind, t.imageId],
foreignColumns: [campaignImages.kind, campaignImages.id],
}).onDelete({ action: "set null", columns: [t.imageId] });
```

…or a parallel method like `.onDeleteSetNull([t.imageId])` if the existing `.onDelete(action: string)` signature is hard to widen without breakage. The same shape would apply to `set default`.

## Version

Reproduced on `drizzle-orm@1.0.0-beta.21`, `drizzle-kit@1.0.0-beta.21`. Type definition cited from `node_modules/drizzle-orm/pg-core/foreign-keys.d.ts:6`.

## Workaround (for anyone hitting this before the fix)

Use `'no action'` on the composite FK and clear the referencing column at the application layer before deleting the referenced row. The FK blocks the delete until the app clears the reference, so the wrong order is impossible.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.