issues bigint batch 2: add incoming foreign keys on issues.id

Everyone can contribute. Help move this issue forward while earning points, leveling up and collecting rewards.

Add the incoming foreign keys from other tables that reference issues.id, pointed at the bigint column, so the id column can later be swapped. There are around 70 such foreign keys across roughly 68 tables.

Tasks

  • Agree the MR and migration split with the database reviewer (one foreign key per migration versus the merge_requests stage three precedent that grouped many into a single migration, weighed against the foreign key guideline of at most one add per migration)
  • Add each foreign key against id_convert_to_bigint as a temporary constraint and validate it. Whether validation needs its own migration depends on the size of each referencing table, so splitting this is an implementation detail
  • Recreate the batch 1 self-referencing foreign keys (duplicated_to_id, moved_to_id) against the bigint column

Dependencies

Order for batch 2:

  1. Create the bigint indexes — #629828
  2. Add the incoming foreign keys on issues.id (this issue)
  3. Swap id and drop the temporary objects — #630762

Requires the issues_pkey unique index on id_convert_to_bigint from the index issue to exist first, since a foreign key target must be unique.

All of these foreign keys must be present and valid before the swap, because swapping the primary key drops the old constraint with cascade.

Edited by 🤖 GitLab Bot 🤖