Update epics foreign key on parent_id to nullify on delete

What does this MR do and why?

Related to #452189 (closed)

Change the epics foreign key on parent_id to use an ON DELETE SET NULL constraint.

The action of deleting all epic descendants can be very disruptive and affect external groups so it should be implemented as an in-app feature instead of being performed at the DB level. See #452189 (comment 1843977963) for more context.

Database

This MR follows the steps documented in Updating foreign keys in migrations:

1. Add new FK

Migrate

bin/rails db:migrate:up:main VERSION=20240403113607
main: == [advisory_lock_connection] object_id: 121780, pg_backend_pid: 2153
main: == 20240403113607 ReplaceEpicsFkOnParentId: migrating =========================
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("ALTER TABLE epics ADD CONSTRAINT fk_epics_on_parent_id_with_on_delete_nullify FOREIGN KEY (parent_id) REFERENCES epics (id) ON DELETE SET NULL NOT VALID;")
main:    -> 0.0070s
main: == 20240403113607 ReplaceEpicsFkOnParentId: migrated (0.0452s) ================

Rollback

bin/rails db:migrate:down:main VERSION=20240403113607
main: == [advisory_lock_connection] object_id: 121840, pg_backend_pid: 2682
main: == 20240403113607 ReplaceEpicsFkOnParentId: reverting =========================
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- remove_foreign_key(:epics, {:column=>:parent_id, :on_delete=>:nullify, :name=>"fk_epics_on_parent_id_with_on_delete_nullify"})
main:    -> 0.0038s
main: == 20240403113607 ReplaceEpicsFkOnParentId: reverted (0.0501s) ================

2. Validate the new FK

Migrate

bin/rails db:migrate:up:main VERSION=20240403114400
main: == [advisory_lock_connection] object_id: 121780, pg_backend_pid: 4116
main: == 20240403114400 ValidateEpicsFkOnParentIdWithOnDeleteNullify: migrating =====
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- execute("ALTER TABLE epics VALIDATE CONSTRAINT fk_epics_on_parent_id_with_on_delete_nullify;")
main:    -> 0.0053s
main: -- execute("RESET statement_timeout")
main:    -> 0.0002s
main: == 20240403114400 ValidateEpicsFkOnParentIdWithOnDeleteNullify: migrated (0.0322s)

main: == [advisory_lock_connection] object_id: 121780, pg_backend_pid: 4116

Rollback

 bin/rails db:migrate:down:main VERSION=20240403114400
main: == [advisory_lock_connection] object_id: 121780, pg_backend_pid: 4577
main: == 20240403114400 ValidateEpicsFkOnParentIdWithOnDeleteNullify: reverting =====
main: == 20240403114400 ValidateEpicsFkOnParentIdWithOnDeleteNullify: reverted (0.0038s)

main: == [advisory_lock_connection] object_id: 121780, pg_backend_pid: 4577

3. Remove the old FK

Migrate

bin/rails db:migrate:up:main VERSION=20240403114900
main: == [advisory_lock_connection] object_id: 121780, pg_backend_pid: 6614
main: == 20240403114900 RemoveFkEpicsParentId: migrating ============================
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- remove_foreign_key(:epics, {:column=>:parent_id, :on_delete=>:cascade, :name=>:fk_25b99c1be3})
main:    -> 0.0033s
main: == 20240403114900 RemoveFkEpicsParentId: migrated (0.0368s) ===================

main: == [advisory_lock_connection] object_id: 121780, pg_backend_pid: 6614

Rollback

bin/rails db:migrate:down:main VERSION=20240403114900
main: == [advisory_lock_connection] object_id: 121780, pg_backend_pid: 7131
main: == 20240403114900 RemoveFkEpicsParentId: reverting ============================
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("ALTER TABLE epics ADD CONSTRAINT fk_25b99c1be3 FOREIGN KEY (parent_id) REFERENCES epics (id) ON DELETE CASCADE NOT VALID;")
main:    -> 0.0015s
main: == 20240403114900 RemoveFkEpicsParentId: reverted (0.0500s) ===================

main: == [advisory_lock_connection] object_id: 121780, pg_backend_pid: 7131

MR acceptance checklist

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