Foreign Key Constraint Violation with Blocks Used Across Multiple Collections

[technicaldirector]

Describe the Bug

When using the same block (with a custom dbName) across multiple collections that have versioning enabled, PayloadCMS creates a foreign key constraint that only references one parent table, causing database errors when trying to save documents in other collections.

2025-10-25.18-20-27.mp4

Link to the code that reproduces this issue

https://github.com/technicaldirector/payload-block-issue

Reproduction Steps

1. Create a block with a custom dbName property:

export const RelatedProjectsBlock: Block = {
  slug: 'relatedProjectsBlock',
  dbName: 'relProjBlk',  // Custom short name
  interfaceName: 'RelatedProjects',
  fields: [/* ... */]
}

2. Use this block in multiple collections with versioning enabled:

// Pages Collection
export const Pages: CollectionConfig = {
  slug: 'pages',
  versions: {
    drafts: { autosave: { interval: 100 } },
    maxPerDoc: 50,
  },
  fields: [
    {
      name: 'layout',
      type: 'blocks',
      blocks: [RelatedProjectsBlock, /* other blocks */]  // Using blocks, not blockReferences
    }
  ]
}

// Projects Collection
export const Projects: CollectionConfig = {
  slug: 'projects',
  versions: {
    drafts: { autosave: { interval: 100 } },
    maxPerDoc: 50,
  },
  fields: [
    {
      name: 'layout',
      type: 'blocks',
      blocks: [RelatedProjectsBlock, /* other blocks */]  // Using blocks, not blockReferences
    }
  ]
}

3. Create or edit a document in the Projects collection
4. Add a RelatedProjectsBlock to the layout
5. Try to save the document (autosave or manual save)

Expected Behavior

The document should save successfully, and the version table should correctly reference the parent document in the _projects_v table.

Actual Behavior

The save operation fails with a foreign key constraint violation:

ERROR: There was an error while saving a version for the Project with ID 1.
DatabaseError: insert or update on table "_relProjBlk_v" violates foreign key constraint "_relProjBlk_v_parent_id_fk"

Detail: Key (_parent_id)=(38) is not present in table "_pages_v".

The error indicates that the foreign key constraint _relProjBlk_v_parent_id_fk is pointing to the _pages_v table instead of the _projects_v table.

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

db: postgres, area: core

Environment Info

- Payload Version: 3.61.1
- Next.js Version: 15.3.0
- Node Version: node:22.14.0-alpine

[mikexavier]

I'm experiencing the same issue with Payload 3.59.1.

My scenario:

• Block: AccessibilityArchiveBlock with dbName: 'a11y-archive'
• Used in Pages collection (with versioning enabled)
• Trying to add the same block to Accessibility collection (also with versioning enabled)

Error when running migration:

insert or update on table "a11y-archive" violates foreign key constraint "a11y-archive_parent_id_fk"
Detail: Key (_parent_id)=(X) is not present in table "a11y"

The migration tries to DROP the constraint pointing to pages and ADD a new one pointing to a11y, but
existing data prevents this.

[technicaldirector]

I think I found the issue. When you add a dbName to a block, PayloadCMS creates only one table for that block across all collections:

With dbName:
dbName: 'relProjBlk'

• Pages collection → relProjBlk
• Projects collection → relProjBlk (same table!)

Without dbName:

• Pages collection → pages_blocks_relatedProjectsBlock
• Projects collection → projects_blocks_relatedProjectsBlock

So when there's no custom dbName, each collection gets its own block table and each can have its own foreign key pointing to the correct parent. But with a customdbName, there's only one table, so it creates a FK to the first collection it encounters (e.g., pages), and then when the second collection (e.g., projects) tries to save, it fails because the FK is pointing to the wrong parent.

Scenario	Table Names	FK Constraints	Works?
No dbName	pages_blocks_relProj + projects_blocks_relProj	Each has its own FK	Yes
With dbName (current)	relProjBlk (shared)	Only one FK to first parent	No

The issue is that SQL foreign keys work at the table level, not per row. You can't have one FK constraint that points to different tables depending on which row you're looking at.

Solution can be

Skip FK creation for blocks that have a custom dbName since they're basically polymorphic relationships (one table, multiple parents) which FK constraints can't handle anyway. PayloadCMS already does referential integrity checks in the application code, so we're not losing much.

Files I patched:

1. node_modules/@payloadcms/drizzle/dist/schema/buildRawSchema.js

   • Made blocksTableNameMap global so it's shared across all collections
   • Lines: 14, 47, 63, 85, 106

2. node_modules@payloadcms\drizzle\dist\schema\traverseFields.js

   • Added logic to detect blocks with custom dbName
   • Skip FK creation when dbName is set
   • Lines: 289-351

I appended my files to the comment. - If that solution sounds reasonable hit me up and i will create a pr. Or perhabs there are better solutions?

traverseFields.js
buildRawSchema.js