Step 3: Add composite foreign keys with partition_id for vulnerability_identifiers references

Summary

Add partition_id to foreign keys that reference vulnerability_identifiers, using composite FKs with on_update: :cascade.

Two tables reference vulnerability_identifiers:

Source table	Source column	FK name	On delete	Status
vulnerability_occurrence_identifiers	identifier_id	fk_rails_be2e49e1d0	CASCADE	✅ Done — replaced by composite fk_rails_be2e49e1d0_p
vulnerability_occurrences	primary_identifier_id	fk_rails_c8661a61eb	CASCADE	✅ Done — replaced by composite fk_rails_c8661a61eb_p

Current database state (verified 2026-08-19)

• vulnerability_occurrence_identifiers has a partition_id column (DEFAULT 1) with a validated NOT NULL check constraint (check_aacd1ff57e) ✅
• vulnerability_occurrences.partition_id has a validated NOT NULL check constraint (check_3225d02bda) ✅
• vulnerability_identifiers has a partition_id column (DEFAULT 1, nullable) with unique index (id, partition_id) ✅
• Composite FK fk_rails_be2e49e1d0_p (partition_id, identifier_id) → vulnerability_identifiers(partition_id, id) with ON UPDATE CASCADE ON DELETE CASCADE exists and is validated ✅
• Old single-column FK fk_rails_be2e49e1d0 has been removed ✅
• Composite FK fk_rails_c8661a61eb_p (partition_id, primary_identifier_id) → vulnerability_identifiers(partition_id, id) with ON UPDATE CASCADE ON DELETE CASCADE exists and is validated (added NOT VALID in !244081 (merged), async validation in !247860 (merged), sync validation in !247863 (merged)) ✅
• Supporting index idx_vuln_occurrences_on_partition_id_primary_identifier_id on vulnerability_occurrences (partition_id, primary_identifier_id) created (async !244112 (merged), sync !244113 (merged)) ✅
• Old single-column FK fk_rails_c8661a61eb on vulnerability_occurrences.primary_identifier_id has been removed (!247863 (merged)) ✅
• vulnerability_occurrence_identifiers also has FK fk_rails_e4ef6d027c → vulnerability_occurrences(id) ON DELETE CASCADE

Steps

For vulnerability_occurrence_identifiers: ✅ Complete

1. Add partition_id column with DEFAULT 1, NOT NULL to vulnerability_occurrence_identifiers — !237458 (merged), !240979 (merged), !242871 (merged)
2. Add composite FK (partition_id, identifier_id) → vulnerability_identifiers(partition_id, id) with on_update: :cascade, on_delete: :cascade — !237458 (merged)
3. Validate the FK — async in !237458 (merged), sync in !240976 (merged)
4. Remove old single-column FK fk_rails_be2e49e1d0 on identifier_id — !240976 (merged)

For vulnerability_occurrences: ✅ Complete

1. Add NOT NULL constraint on partition_id — done via !240991 (merged) and !242869 (merged)
2. Add composite FK (partition_id, primary_identifier_id) → vulnerability_identifiers(partition_id, id) with on_update: :cascade, on_delete: :cascade (NOT VALID) — !244081 (merged) (merged 2026-08-04)
3. Add supporting index on (partition_id, primary_identifier_id) — async !244112 (merged), sync !244113 (merged)
4. Validate the FK — async validation in !247860 (merged), sync validation in !247863 (merged) (merged 2026-08-19)
5. Remove old single-column FK fk_rails_c8661a61eb on primary_identifier_id — !247863 (merged) (merged 2026-08-19)

Reference

• List partitioning docs — Step 4: Enforce foreign key constraint
• Example: db/post_migrate/20260407120144_create_missing_fk_and_analyze_p_ci_build_needs.rb
• Completed example for this issue: MRs !237458 (merged), !240976 (merged), !240979 (merged), !240991 (merged), !242869 (merged), !242871 (merged)

Notes

• The on_update: :cascade is mandatory — without it, updating the partition column would fail with referential integrity errors
• This step depends on the unique index (id, partition_id) on vulnerability_identifiers already being in place (✅ done)
• vulnerability_identifiers.partition_id is still nullable — ensure this is addressed in the partitioning work for the referenced table
• The vulnerability_occurrence_identifiers table also has a FK fk_rails_e4ef6d027c to vulnerability_occurrences(id) — if vulnerability_occurrences is also being partitioned, the FKs there may need to be updated in coordination

Acceptance criteria

• vulnerability_occurrence_identifiers has partition_id column (DEFAULT 1, NOT NULL)
• vulnerability_occurrences.partition_id has NOT NULL constraint
• Composite FK from vulnerability_occurrence_identifiers(partition_id, identifier_id) → vulnerability_identifiers(partition_id, id) exists and is validated
• Composite FK from vulnerability_occurrences(partition_id, primary_identifier_id) → vulnerability_identifiers(partition_id, id) exists and is validated (added NOT VALID in !244081 (merged), validated via !247860 (merged) / !247863 (merged))
• Old single-column FKs are removed (fk_rails_be2e49e1d0 removed via !240976 (merged), fk_rails_c8661a61eb removed via !247863 (merged))