Add a `NOT VALID` foreign key on `Project#creator_id`
What does this MR do and why?
This MR originates from the discussion in #378476 (comment 1153919092)
This adds a ON DELETE SET NULL
, but NOT VALID
foreign key relationship in the Project#creator_id
column, pointing to the User#id
column.
On the model, this is the relationship between the two tables.
Why NOT VALID
? - because there are existing records in the projects
table that point to already deleted users, and that needs to be cleaned up (#378477 (closed)), before we can validate this constraint.
Follow up:
- To clean existing, incorrect records: #378477 (closed) (in %15.6)
- To validate the relationship: #379677 (closed) (in %15.7)
UP Migration
$ rails db:migrate
main: == 20221031102916 AddUsersForeignKeyToProjects: migrating =====================
main: -- transaction_open?()
main: -> 0.0000s
main: -- foreign_keys(:projects)
main: -> 0.0119s
main: -- transaction_open?()
main: -> 0.0000s
main: -- execute("ALTER TABLE projects\nADD CONSTRAINT fk_03ec10b0d3\nFOREIGN KEY (creator_id)\nREFERENCES users (id)\nON DELETE SET NULL\nNOT VALID;\n")
main: -> 0.0031s
main: == 20221031102916 AddUsersForeignKeyToProjects: migrated (0.0273s) ============
ci: == 20221031102916 AddUsersForeignKeyToProjects: migrating =====================
ci: -- transaction_open?()
ci: -> 0.0000s
ci: -- foreign_keys(:projects)
ci: -> 0.0057s
ci: -- transaction_open?()
ci: -> 0.0000s
ci: -- execute("ALTER TABLE projects\nADD CONSTRAINT fk_03ec10b0d3\nFOREIGN KEY (creator_id)\nREFERENCES users (id)\nON DELETE SET NULL\nNOT VALID;\n")
ci: -> 0.0064s
ci: == 20221031102916 AddUsersForeignKeyToProjects: migrated (0.0136s) ============
DOWN Migration
$ rake db:migrate:down:main VERSION=20221031102916
main: == 20221031102916 AddUsersForeignKeyToProjects: reverting =====================
main: -- transaction_open?()
main: -> 0.0000s
main: -- foreign_keys(:projects)
main: -> 0.0080s
main: -- remove_foreign_key(:projects, {:column=>:creator_id})
main: -> 0.0068s
main: == 20221031102916 AddUsersForeignKeyToProjects: reverted (0.0227s) ============
$ rake db:migrate:down:ci VERSION=20221031102916
ci: == 20221031102916 AddUsersForeignKeyToProjects: reverting =====================
ci: -- transaction_open?()
ci: -> 0.0000s
ci: -- foreign_keys(:projects)
ci: -> 0.0091s
ci: -- remove_foreign_key(:projects, {:column=>:creator_id})
ci: -> 0.0089s
ci: == 20221031102916 AddUsersForeignKeyToProjects: reverted (0.0251s) ============
Screenshots or screen recordings
Screenshots are required for UI changes, and strongly recommended for all other merge requests.
How to set up and validate locally
Numbered steps to set up and validate the change are strongly suggested.
MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.