Replace epics issue_id foreign key with new key nullifies on delete
What does this MR do and why?
Related to #436381 (closed)
In !139788 (merged) we added the column issue_id
to epics with a foreign key using on_delete: :cascade
but we don't want to delete the epic in case of issue deletion so we need to add a new foreign key that nullifies the column instead.
This MR follows the steps recommended for updating foreign keys:
-
Add the new foreign key without validation
20231227103059_replace_fk_on_epics_issue_id.rb
Migrate
❯ bin/rails db:migrate:up:main VERSION=20231227103059 main: == [advisory_lock_connection] object_id: 182380, pg_backend_pid: 18428 main: == 20231227103059 ReplaceFkOnEpicsIssueId: migrating ========================== main: -- transaction_open?(nil) main: -> 0.0000s main: -- transaction_open?(nil) main: -> 0.0000s main: -- execute("ALTER TABLE epics ADD CONSTRAINT fk_epics_issue_id_with_on_delete_nullify FOREIGN KEY (issue_id) REFERENCES issues (id) ON DELETE SET NULL NOT VALID;") main: -> 0.0020s main: == 20231227103059 ReplaceFkOnEpicsIssueId: migrated (0.1396s) =================
Rollback
❯ bin/rails db:migrate:down:main VERSION=20231227103059 main: == [advisory_lock_connection] object_id: 182480, pg_backend_pid: 17847 main: == 20231227103059 ReplaceFkOnEpicsIssueId: reverting ========================== main: -- transaction_open?(nil) main: -> 0.0000s main: -- remove_foreign_key(:epics, {:column=>:issue_id, :on_delete=>:nullify, :name=>:fk_epics_issue_id_with_on_delete_nullify}) main: -> 0.0027s main: == 20231227103059 ReplaceFkOnEpicsIssueId: reverted (0.1152s) =================
-
Validate the new foreign key
20231227104408_validate_fk_epics_issue_id_with_on_delete_nullify.rb
Migrate
❯ bin/rails db:migrate:up:main VERSION=20231227104408 main: == [advisory_lock_connection] object_id: 182380, pg_backend_pid: 19692 main: == 20231227104408 ValidateFkEpicsIssueIdWithOnDeleteNullify: migrating ======== main: -- execute("SET statement_timeout TO 0") main: -> 0.0003s main: -- execute("ALTER TABLE epics VALIDATE CONSTRAINT fk_epics_issue_id_with_on_delete_nullify;") main: -> 0.0052s main: -- execute("RESET statement_timeout") main: -> 0.0007s main: == 20231227104408 ValidateFkEpicsIssueIdWithOnDeleteNullify: migrated (0.1454s) main: == [advisory_lock_connection] object_id: 182380, pg_backend_pid: 19692
Rollback
❯ bin/rails db:migrate:down:main VERSION=20231227104408 main: == [advisory_lock_connection] object_id: 182440, pg_backend_pid: 19180 main: == 20231227104408 ValidateFkEpicsIssueIdWithOnDeleteNullify: reverting ======== main: == 20231227104408 ValidateFkEpicsIssueIdWithOnDeleteNullify: reverted (0.0040s) main: == [advisory_lock_connection] object_id: 182440, pg_backend_pid: 19180
-
Remove the old foreign key
20231227104711_remove_fk_epics_issue_id.rb
Migrate
❯ bin/rails db:migrate:up:main VERSION=20231227104711 main: == [advisory_lock_connection] object_id: 182380, pg_backend_pid: 21678 main: == 20231227104711 RemoveFkEpicsIssueId: migrating ============================= main: -- transaction_open?(nil) main: -> 0.0000s main: -- remove_foreign_key(:epics, {:column=>:issue_id, :on_delete=>:cascade, :name=>:fk_893ee302e5}) main: -> 0.0034s main: == 20231227104711 RemoveFkEpicsIssueId: migrated (0.1490s) ==================== main: == [advisory_lock_connection] object_id: 182380, pg_backend_pid: 21678
Rollback
❯ bin/rails db:migrate:down:main VERSION=20231227104711 main: == [advisory_lock_connection] object_id: 189220, pg_backend_pid: 22201 main: == 20231227104711 RemoveFkEpicsIssueId: reverting ============================= main: -- transaction_open?(nil) main: -> 0.0000s main: -- transaction_open?(nil) main: -> 0.0000s main: -- execute("ALTER TABLE epics ADD CONSTRAINT fk_893ee302e5 FOREIGN KEY (issue_id) REFERENCES issues (id) ON DELETE CASCADE NOT VALID;") main: -> 0.0014s main: == 20231227104711 RemoveFkEpicsIssueId: reverted (0.1470s) ==================== main: == [advisory_lock_connection] object_id: 189220, pg_backend_pid: 22201
Note that the column epics.issue_id
is not yet in use, it will start being used with !140232 (merged).
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.