Update referenced FK to include partition_id for ci_pipelines

Problem

Each referenced FK must include the partitioning key in order to partition ci_pipelines.

Solution

Rewrite the following FKs to include partition_id.

Referenced by:
    TABLE "ci_pipeline_chat_data" CONSTRAINT "fk_64ebfab6b3" FOREIGN KEY (pipeline_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE
    TABLE "p_ci_builds" CONSTRAINT "fk_87f4cefcda" FOREIGN KEY (upstream_pipeline_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE
    TABLE "p_ci_builds" CONSTRAINT "fk_d3130c9a7f" FOREIGN KEY (commit_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE
    TABLE "ci_sources_pipelines" CONSTRAINT "fk_d4e29af7d7" FOREIGN KEY (source_pipeline_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE
    TABLE "ci_sources_pipelines" CONSTRAINT "fk_e1bad85861" FOREIGN KEY (pipeline_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE
    TABLE "p_ci_pipeline_variables" CONSTRAINT "fk_f29c5f4380" FOREIGN KEY (pipeline_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE
    TABLE "p_ci_stages" CONSTRAINT "fk_fb57e6cc56" FOREIGN KEY (pipeline_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE
    TABLE "ci_sources_projects" CONSTRAINT "fk_rails_10a1eb379a" FOREIGN KEY (pipeline_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE
    TABLE "ci_pipeline_metadata" CONSTRAINT "fk_rails_50c1e9ea10" FOREIGN KEY (pipeline_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE
    TABLE "ci_pipeline_messages" CONSTRAINT "fk_rails_8d3b04e3e1" FOREIGN KEY (pipeline_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE
    TABLE "ci_pipelines_config" CONSTRAINT "fk_rails_906c9a2533" FOREIGN KEY (pipeline_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE
    TABLE "ci_pipeline_artifacts" CONSTRAINT "fk_rails_a9e811a466" FOREIGN KEY (pipeline_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE
    TABLE "p_ci_builds_execution_configs" CONSTRAINT "fk_rails_c26408d02c" FOREIGN KEY (pipeline_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE
    TABLE "ci_daily_build_group_report_results" CONSTRAINT "fk_rails_ee072d13b3" FOREIGN KEY (last_pipeline_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE

https://docs.gitlab.com/ee/development/database/table_partitioning.html#step-4---enforce-foreign-key-constraint

See #427882 (closed)

Rewrite foreign key for auto_canceled_by_id to include auto_canceled_by_partition_id and becomes (auto_canceled_by_partition_id, auto_canceled_by_id)

    TABLE "ci_pipelines" CONSTRAINT "fk_262d4c2d19" FOREIGN KEY (auto_canceled_by_id) REFERENCES ci_pipelines(id) ON DELETE SET NULL
    TABLE "p_ci_builds" CONSTRAINT "fk_a2141b1522" FOREIGN KEY (auto_canceled_by_id) REFERENCES ci_pipelines(id) ON DELETE SET NULL

Progress

Partitioned tables

FK	New FK on partitions	Valid?	Replaced FK on routing table
`TABLE "p_ci_builds" CONSTRAINT "fk_a2141b1522" FOREIGN KEY (auto_canceled_by_id) REFERENCES ci_pipelines(id) ON DELETE SET NULL`	!157750 (merged)	✅	!159727 (merged)
`TABLE "p_ci_builds" CONSTRAINT "fk_87f4cefcda" FOREIGN KEY (upstream_pipeline_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE`	!161868 (merged)	✅	!160710 (merged) !162529 (merged)
`TABLE "p_ci_builds" CONSTRAINT "fk_d3130c9a7f" FOREIGN KEY (commit_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE`	!157750 (merged)	✅	!159727 (merged)
`TABLE "p_ci_pipeline_variables" CONSTRAINT "fk_f29c5f4380" FOREIGN KEY (pipeline_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE`	!157747 (merged)	✅	!159725 (merged)
`TABLE "p_ci_stages" CONSTRAINT "fk_fb57e6cc56" FOREIGN KEY (pipeline_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE`	!157753 (merged)	✅	!159725 (merged)
`TABLE "p_ci_builds_execution_configs" CONSTRAINT "fk_rails_c26408d02c" FOREIGN KEY (pipeline_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE`	!157908 (merged)	✅	!157908 (merged)

Regular tables

FK	New FK	Valid?	Replaced FK
`TABLE "ci_pipeline_chat_data" CONSTRAINT "fk_64ebfab6b3" FOREIGN KEY (pipeline_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE`	!157938 (merged)	✅	!157938 (merged)
`TABLE "ci_sources_pipelines" CONSTRAINT "fk_d4e29af7d7" FOREIGN KEY (source_pipeline_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE`	!157947 (merged)	✅	!159555 (merged)
`TABLE "ci_sources_pipelines" CONSTRAINT "fk_e1bad85861" FOREIGN KEY (pipeline_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE`	!157947 (merged)	✅	!159555 (merged)
`TABLE "ci_sources_projects" CONSTRAINT "fk_rails_10a1eb379a" FOREIGN KEY (pipeline_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE`	!158086 (merged)	✅	!158086 (merged)
`TABLE "ci_pipeline_metadata" CONSTRAINT "fk_rails_50c1e9ea10" FOREIGN KEY (pipeline_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE`	!158065 (merged)	✅	!159571 (merged)
`TABLE "ci_pipeline_messages" CONSTRAINT "fk_rails_8d3b04e3e1" FOREIGN KEY (pipeline_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE`	!158065 (merged)	✅	!159571 (merged)
`TABLE "ci_pipelines_config" CONSTRAINT "fk_rails_906c9a2533" FOREIGN KEY (pipeline_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE`	!158065 (merged)	✅	!159571 (merged)
`TABLE "ci_pipeline_artifacts" CONSTRAINT "fk_rails_a9e811a466" FOREIGN KEY (pipeline_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE`	!158086 (merged)	✅	!159571 (merged)
`TABLE "ci_daily_build_group_report_results" CONSTRAINT "fk_rails_ee072d13b3" FOREIGN KEY (last_pipeline_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE`	!158086 (merged)	✅	!160672 (merged) !162529 (merged)
`TABLE "ci_pipelines" CONSTRAINT "fk_262d4c2d19" FOREIGN KEY (auto_canceled_by_id) REFERENCES ci_pipelines(id) ON DELETE SET NULL`	!161872 (merged)	✅	!161287 (merged) !162529 (merged)

Edited Aug 13, 2024 by Tianwen Chen