On fresh schema apply The INSERT statement conflicted with the FOREIGN KEY SAME TABLE constraint "directus_collections_group_foreign"

[dariusj18]

Describe the Bug

When running a schema apply (on a database with pre-existing tables, with a freshly bootstrapped directus, with a schema form another database which was crafted with the same database tables, ex. no database tables are changes, only directus meta data) from the CLI getting the error :

 ERROR: insert into [directus_collections] ([accountability], [archive_app_filter], [archive_field], [archive_value], [collapse], [collection], [color], [display_template], [group], [hidden], [icon], [item_duplication_fields], [note], [preview_url], [singleton], [sort], [sort_field], [translations], [unarchive_value], [versioning]) output inserted.[collection] values (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19) - The INSERT statement conflicted with the FOREIGN KEY SAME TABLE constraint "directus_collections_group_foreign". The conflict occurred in database "DATABASE", table "dbo.directus_collections", column 'collection'.
    err: {
      "type": "RequestError",
      "message": "insert into [directus_collections] ([accountability], [archive_app_filter], [archive_field], [archive_value], [collapse], [collection], [color], [display_template], [group], [hidden], [icon], [item_duplication_fields], [note], [preview_url], [singleton], [sort], [sort_field], [translations], [unarchive_value], [versioning]) output inserted.[collection] values (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19) - The INSERT statement conflicted with the FOREIGN KEY SAME TABLE constraint \"directus_collections_group_foreign\". The conflict occurred in database \"DATABASE\", table \"dbo.directus_collections\", column 'collection'.",
      "stack":
          Error: insert into [directus_collections] ([accountability], [archive_app_filter], [archive_field], [archive_value], [collapse], [collection], [color], [display_template], [group], [hidden], [icon], [item_duplication_fields], [note], [preview_url], [singleton], [sort], [sort_field], [translations], [unarchive_value], [versioning]) output inserted.[collection] values (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19) - The INSERT statement conflicted with the FOREIGN KEY SAME TABLE constraint "directus_collections_group_foreign". The conflict occurred in database "DATABASE", table "dbo.directus_collections", column 'collection'.
              at RequestTokenHandler.onErrorMessage (/Users/USER/Projects/PROJECT/node_modules/.pnpm/[email protected]/node_modules/tedious/lib/token/handler.js:287:21)
              at Readable.<anonymous> (/Users/USER/Projects/PROJECT/node_modules/.pnpm/[email protected]/node_modules/tedious/lib/token/token-stream-parser.js:18:33)
              at Readable.emit (node:events:517:28)
              at addChunk (node:internal/streams/readable:335:12)
              at readableAddChunk (node:internal/streams/readable:308:9)
              at Readable.push (node:internal/streams/readable:245:10)
              at next (node:internal/streams/from:98:31)
              at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
      "code": "EREQUEST",
      "number": 547,
      "state": 0,
      "class": 16,
      "serverName": "833238a4a12b",
      "procName": "",
      "lineNumber": 1
    }

I was able to get around the issue by disabling the FK before running the schema apply

ALTER TABLE [dbo].[directus_collections] NOCHECK CONSTRAINT [directus_collections_group_foreign]

and re-enabling it after

ALTER TABLE [dbo].[directus_collections] CHECK CONSTRAINT [directus_collections_group_foreign]

To Reproduce

Run a fresh schema apply where the order of the collections is such that a collection which has a group is attempted to be added before the collection who's name is referenced.

Directus Version

10.10.5

Hosting Strategy

Self-Hosted (Docker Image)

[br41nslug]

Run a fresh schema apply where the order of the collections is such that a collection which has a group is attempted to be added before the collection who's name is referenced.

Can you provide more detail in the reproduction steps or provide an example schema where this ordering is failing?

[dariusj18]

This will take a bit for me to try and create something to reproduce. My first pass at creating something worked without error, I think in this case it might be the way the two tables are related to each other and also nested as collections.