Skip to content

Add foreign key constraint to `projects.organization_id`

Abdul Wadood requested to merge 466143-projects-organization-id-fk into master

What does this MR do and why?

The code changes add a foreign key constraint to the projects table, linking it to the organizations table based on the organization_id column. This ensures that every project is associated with an organization and prevents orphaned projects.

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.

Migration output

up 
bin/rails db:migrate
main: == [advisory_lock_connection] object_id: 128840, pg_backend_pid: 8443
main: == 20240923055610 AddForeignKeyToProjectsOnOrganizationId: migrating ==========
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("LOCK TABLE organizations, projects IN SHARE ROW EXCLUSIVE MODE")
main:    -> 0.0005s
main: -- execute("ALTER TABLE projects ADD CONSTRAINT fk_9aee26923d FOREIGN KEY (organization_id) REFERENCES organizations (id) ON DELETE CASCADE NOT VALID;")
main:    -> 0.0052s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- execute("ALTER TABLE projects VALIDATE CONSTRAINT fk_9aee26923d;")
main:    -> 0.0053s
main: -- execute("RESET statement_timeout")
main:    -> 0.0004s
main: == 20240923055610 AddForeignKeyToProjectsOnOrganizationId: migrated (0.0592s) =

main: == [advisory_lock_connection] object_id: 128840, pg_backend_pid: 8443
ci: == [advisory_lock_connection] object_id: 129160, pg_backend_pid: 8445
ci: == 20240923055610 AddForeignKeyToProjectsOnOrganizationId: migrating ==========
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- execute("LOCK TABLE organizations, projects IN SHARE ROW EXCLUSIVE MODE")
ci:    -> 0.0005s
ci: -- execute("ALTER TABLE projects ADD CONSTRAINT fk_9aee26923d FOREIGN KEY (organization_id) REFERENCES organizations (id) ON DELETE CASCADE NOT VALID;")
ci:    -> 0.0054s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0003s
ci: -- execute("ALTER TABLE projects VALIDATE CONSTRAINT fk_9aee26923d;")
ci:    -> 0.0101s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0007s
ci: == 20240923055610 AddForeignKeyToProjectsOnOrganizationId: migrated (0.0452s) =

ci: == [advisory_lock_connection] object_id: 129160, pg_backend_pid: 8445
down 
for v in 20240923055610; do bin/rails db:migrate:down:ci VERSION=$v && bin/rails db:migrate:down:main VERSION=$v; done
ci: == [advisory_lock_connection] object_id: 128400, pg_backend_pid: 7629
ci: == 20240923055610 AddForeignKeyToProjectsOnOrganizationId: reverting ==========
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- remove_foreign_key(:projects, {:column=>:organization_id})
ci:    -> 0.0043s
ci: == 20240923055610 AddForeignKeyToProjectsOnOrganizationId: reverted (0.0544s) =

ci: == [advisory_lock_connection] object_id: 128400, pg_backend_pid: 7629
main: == [advisory_lock_connection] object_id: 128400, pg_backend_pid: 8020
main: == 20240923055610 AddForeignKeyToProjectsOnOrganizationId: reverting ==========
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- remove_foreign_key(:projects, {:column=>:organization_id})
main:    -> 0.0151s
main: == 20240923055610 AddForeignKeyToProjectsOnOrganizationId: reverted (0.0832s) =

main: == [advisory_lock_connection] object_id: 128400, pg_backend_pid: 8020

How to set up and validate locally

Run bin/rails db:migrate

Related to #466143 (closed)

Merge request reports