## 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, async validation in !247860, sync validation in !247863) ✅ - Supporting index `idx_vuln_occurrences_on_partition_id_primary_identifier_id` on `vulnerability_occurrences (partition_id, primary_identifier_id)` created (async !244112, sync !244113) ✅ - Old single-column FK `fk_rails_c8661a61eb` on `vulnerability_occurrences.primary_identifier_id` has been **removed** (!247863) ✅ - `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, !240979, !242871 2. ~~Add composite FK `(partition_id, identifier_id)` → `vulnerability_identifiers(partition_id, id)` with `on_update: :cascade, on_delete: :cascade`~~ — !237458 3. ~~Validate the FK~~ — async in !237458, sync in !240976 4. ~~Remove old single-column FK `fk_rails_be2e49e1d0` on `identifier_id`~~ — !240976 ### For `vulnerability_occurrences`: ✅ Complete 1. ~~Add NOT NULL constraint on `partition_id`~~ — done via !240991 and !242869 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 2026-08-04) 3. ~~Add supporting index on `(partition_id, primary_identifier_id)`~~ — async !244112, sync !244113 4. ~~Validate the FK~~ — async validation in !247860, sync validation in !247863 (merged 2026-08-19) 5. ~~Remove old single-column FK `fk_rails_c8661a61eb` on `primary_identifier_id`~~ — !247863 (merged 2026-08-19) ## Reference - [List partitioning docs — Step 4: Enforce foreign key constraint](https://docs.gitlab.com/development/database/partitioning/list#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, !240976, !240979, !240991, !242869, !242871 ## 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 - [x] `vulnerability_occurrence_identifiers` has `partition_id` column (DEFAULT 1, NOT NULL) - [x] `vulnerability_occurrences.partition_id` has NOT NULL constraint - [x] Composite FK from `vulnerability_occurrence_identifiers(partition_id, identifier_id)` → `vulnerability_identifiers(partition_id, id)` exists and is validated - [x] Composite FK from `vulnerability_occurrences(partition_id, primary_identifier_id)` → `vulnerability_identifiers(partition_id, id)` exists and is validated (added `NOT VALID` in !244081, validated via !247860 / !247863) - [x] Old single-column FKs are removed (`fk_rails_be2e49e1d0` removed via !240976, `fk_rails_c8661a61eb` removed via !247863)