foreign keys with cascade delete

[uncor3]

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