payload foreign keys with cascade delete

Describe the Bug

Hey first of all I've almost finished a production ready site with payloadcms thanks for open sourcing i think it's one of the best cmses I've ever used let me get to the issue currently there seems to be no way to set onUpdate or onDelete behaviors on relationships from collection configs meaning if you set a relationship to be required and try to delete the foreign key you will get this

[11:21:31] ERROR: null value in column "user_id" of relation "comments" violates not-null constraint
    err: {
      "type": "DatabaseError",
      "message": "null value in column \"user_id\" of relation \"comments\" violates not-null constraint",
//removed unnecessary lines for readability

How to reproduce ?

I will link the repo anyways but i don't think you need to reproduce it to understand whats wrong

Here is an example

src/collections/Comments.ts

import type { CollectionConfig } from 'payload'

import { authenticated } from '../../access/authenticated'

export const Comments: CollectionConfig = {
  slug: 'comments',
  access: {
    admin: authenticated,
    create: authenticated,
    delete: authenticated,
    read: authenticated,
    update: authenticated,
  },
  fields: [
    {
      name: 'user',
      type: 'relationship',
      relationTo: 'users',
      required: true,
      //^important
    },
    {
      name: 'content',
      type: 'text',
      required: true,
    },
  ],
  timestamps: true,
}

Now try to delete the user of this comment and you will get the error

[11:21:31] ERROR: null value in column "user_id" of relation "comments" violates not-null constraint err: { "type": "DatabaseError", "message": "null value in column "user_id" of relation "comments" violates not-null constraint", //removed unnecessary lines for readability

Workaround for now

in payload.config.ts

db: postgresAdapter({
    pool: {
      connectionString: process.env.DATABASE_URI || '',
    },
    afterSchemaInit: [
      ({ schema }) => {
        const relations = ['relations_comments']

        relations.forEach((relation) => {
          const index = Symbol.for(`drizzle:PgInlineForeignKeys`)
          //@ts-expect-error workaround
          const fkeys = schema.relations[relation].table[index]
          // Loop through the foreign keys and modify them
          //@ts-expect-error workaround
          fkeys.forEach((foreignKey) => {
            foreignKey.onDelete = 'CASCADE'
            foreignKey.onUpdate = 'CASCADE'
          })
        })
        return schema
      },
    ],
  }),

Link to the code that reproduces this issue

https://github.com/uncor3/payload-issue

Reproduction Steps

-create a user so you do not have to delete the admin user -create a comment with user relation set to the newly created user -try to delete the user it will fail -uncomment the lines in payload.config.ts and restart the app -try to delete now it will work

Which area(s) are affected? (Select all that apply)

area: core

Environment Info

Binaries:
  Node: 20.11.1
  npm: N/A
  Yarn: N/A
  pnpm: 9.6.0
Relevant Packages:
  payload: 3.23.0
  next: 15.1.7
  @payloadcms/db-postgres: 3.23.0
  @payloadcms/email-nodemailer: 3.23.0
  @payloadcms/graphql: 3.23.0
  @payloadcms/live-preview: 3.23.0
  @payloadcms/live-preview-react: 3.23.0
  @payloadcms/next/utilities: 3.23.0
  @payloadcms/payload-cloud: 3.23.0
  @payloadcms/plugin-form-builder: 3.23.0
  @payloadcms/plugin-nested-docs: 3.23.0
  @payloadcms/plugin-redirects: 3.23.0
  @payloadcms/plugin-search: 3.23.0
  @payloadcms/plugin-seo: 3.23.0
  @payloadcms/richtext-lexical: 3.23.0
  @payloadcms/translations: 3.23.0
  @payloadcms/ui/shared: 3.23.0
  react: 19.0.0
  react-dom: 19.0.0
Operating System:
  Platform: win32
  Arch: x64
  Version: Windows 11 Pro
  Available memory (MB): 32678
  Available CPU cores: 12

Feb 14 '25 09:02 uncor3

I have a similar use case and found your workaround interesting. I ended up transforming it into a utility function so I can target the specific foreign key I want to update (solution at the end).

I wonder—isn't this a common use case in any app with relationships?

My question to the Payload team: Is this not implemented in the Payload config because there's a better way to do it, or is it in the backlog? Maybe @jmikrut will know.

I'm asking because I'm trying to understand the correct approach. Based on my experience with other CMSs (like Directus), it's common to have onUpdate and onDelete properties at the schema/collection level to handle this configuration.

I'd love if Payload adds support for something like:

{
    name: "createdBy",
    type: "relationship",
    relationTo: "users",
    hasMany: false,
    required: true,
    onDelete: "cascade"
},

Utility workaround:

export function updateFk(
  // eslint-disable-next-line @typescript-eslint/no-explicit-any
  schema: any,
  tableName: string,
  tableColumnName: string,
  foreignTableName: string,
  foreignColumnName: string,
  onUpdate: string,
  onDelete: string,
) {
  const indexInlineFKs = Symbol.for(`drizzle:PgInlineForeignKeys`);
  const indexTableName = Symbol.for(`drizzle:Name`);

  const fkeys =
    schema.relations[`relations_${tableName}`].table[indexInlineFKs];

  // @ts-expect-error workaround
  fkeys.forEach((foreignKey) => {
    const ref = foreignKey.reference();

    if (ref.columns.length !== 1) {
      throw new Error("Only one column is supported");
    }

    if (ref.foreignColumns.length !== 1) {
      throw new Error("Only one foreign column is supported");
    }

    const actualColumnName = ref.columns[0].name;
    const actualForeignTableName = ref.foreignTable[indexTableName];
    const actualForeignColumnName = ref.foreignColumns[0].name;

    if (actualColumnName !== tableColumnName) return;
    if (actualForeignTableName !== foreignTableName) return;
    if (actualForeignColumnName !== foreignColumnName) return;

    foreignKey.onUpdate = onUpdate;
    foreignKey.onDelete = onDelete;
  });
}

Note: This needs to be updated to support payload versioning

Mar 05 '25 22:03 Saissaken

Ty for your util workaround @Saissaken

I wonder—isn't this a common use case in any app with relationships?

Yes it is. I do not understand how it's not implemented in such a great cms, it's been 3 weeks since i submitted the issue and no maintainer has responded so far.

It may be because they support multiple DBs and they couldn't get it stable

I'd love if Payload adds support for something like:

{
    name: "createdBy",
    type: "relationship",
    relationTo: "users",
    hasMany: false,
    required: true,
    onDelete: "cascade"
}

I agree with this ^

Mar 10 '25 03:03 uncor3

push+ just came accross this. If not implemented I would also be ok with a concept guideline on how to deal with it in best practices.

May 15 '25 08:05 SebastianSchuetze

Yes please! This is a common pattern that we'd ideally need some docs and/or functionality for.

May 19 '25 11:05 willemmulder

@DanRibbens - I believe you were looking into this in https://github.com/payloadcms/payload/pull/1209. Any idea if there are any plans to make this happen?

May 21 '25 14:05 engin-can

+1

Aug 20 '25 11:08 cdelacombaz

+1

Aug 21 '25 10:08 gwesterman

+1

Aug 21 '25 23:08 tonytkachenko

+1

Sep 17 '25 11:09 robotaref

+1

Sep 19 '25 13:09 liamdefty

+1

Sep 26 '25 08:09 ignissak

I was able to achieve this with a "logical cascade delete hook".

In my case, the code is not exactly the same — I have a helper function that creates collections, and within it I added a cascadeDelete parameter that add the actual hook.

This is a conservative approach, but we could also consider a more generic one, such as adding a parameter directly to the relationship field or even "discovering" automatically which relationship types should trigger cascade deletes.

What I like about this approach is that, instead of performing a raw cascade delete, we ensure the collection lifecycle by invoking its hooks.

type CascadeDeleteProps = {
  collection: CollectionSlug;
  on?: string;
}

// "appointments" collection ...
beforeDelete: [
  async (args) => { 
    const cascadeDeletes: CascadeDeleteProps[] = [
      {
        collection: 'appointment_slots',
        on: 'appointment',
      },
    ];

    const { req, id, collection } = args;
    const { payload, transactionID } = req;

    // Check if there is a transaction
    if (! transactionID) {
      console.error('[CascadeDelete] It\'s not safe to proceed without a transaction.');
      return;
    }

    try {
      // Check if there is a transaction
      if (! req.transactionID) {
        console.error('[CascadeDelete] It\'s not safe to proceed without a transaction.');
        throw new Error( 'Error deleting appointments.' );
      }
    
      for (let i = 0; i < cascadeDeletes.length; i++) {
        const { collection: onDeleteCollection, on } = cascadeDeletes[i];
        const column = on ?? collection.slug;
    
        const { errors } = await req.payload.delete({
          collection: onDeleteCollection,
          where: { [column]: { equals: id } },
          req,
        });
    
        // With errors, throw the first one
        if (errors && errors.length > 0) {
          throw new Error( errors[0].message || 'Error deleting appointments.' );
        }
      }
    } catch (error) {
      payload.db.rollbackTransaction(transactionID);
      throw error;
    }
  }
]