Drop hard FK to organizations from four snippet child tables

Chen Zhangrequested to merge
duo/fix/613747-drop-snippet-org-fks into master

What does this MR do and why?

Drop the hard foreign keys from snippet_organization_id to organizations on four snippet child tables:

  • snippet_repositories (fk_efaf4ac269)
  • snippet_statistics (fk_73a34da7d8)
  • snippet_user_mentions (fk_def441dfc3)
  • snippet_repository_storage_moves (fk_321e6c6235)

Root cause: snippets.organization_id has no hard FK to organizations — only a loose FK (async_delete in config/gitlab_loose_foreign_keys.yml), chosen deliberately to avoid synchronous cascading deletes on organization removal. The four child tables copy that value via AssignDesiredShardingKey triggers into a hard-FK'd snippet_organization_id column. When an organization is deleted, the snippet's organization_id can become dangling before the LFK cleanup runs, causing PG::ForeignKeyViolation on any child write (same failure shape as https://gitlab.com/gitlab-org/gitlab/-/issues/605940).

Fix: Drop the children's FKs to organizations. All four tables already have hard snippet_id -> snippets ON DELETE CASCADE FKs, so row cleanup still happens when the snippet is removed.

Precedent: #606941 (closed) (same pattern for packages_nuget_symbol_states and packages_package_file_states, landed in commit cb0b9415a3c8).

Out of scope (pending scope decision in #611521):

  • snippet_repository_states.snippet_organization_id (fk_634bc9f2e3)
  • snippet_uploads.organization_id (fk_rails_e5afc14eb7)
  • personal_snippet_upload_states.organization_id (fk_a7280feb75)

References

Screenshots or screen recordings

N/A — schema-only change, no UI impact.

How to set up and validate locally

  1. Run the migrations: bundle exec rails db:migrate
  2. Confirm the four constraints are gone: check db/structure.sql no longer contains fk_efaf4ac269, fk_73a34da7d8, fk_def441dfc3, fk_321e6c6235.
  3. Run the affected specs: bundle exec rspec spec/db/schema_spec.rb spec/lib/gitlab/organizations/sharding_key_spec.rb

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Merge request reports