Add ON DELETE constraint to security_policy_management_project_id
What does this MR do and why?
Addresses #340846 (closed)
When deleting a security policy project it fails with:
PG::ForeignKeyViolation: ERROR: update or delete on table "projects" violates foreign key constraint "fk_rails_42ed6c25ec" on table "security_orchestration_policy_configurations" DETAIL: Key (id)=(64) is still referenced from table "security_orchestration_policy_configurations".
This is because security_policy_management_project_id
have ON DELETE RESTRICT
constraint
This MR updates the constraint to ON DELETE CASCADE
.
Migrate Up
$ bundle exec rake db:migrate:up VERSION=20210929115340
== 20210929115340 AddSecurityPolicyConfigurationsManagementProjectIdForeignKey: migrating
-- transaction_open?()
-> 0.0000s
-- foreign_keys(:security_orchestration_policy_configurations)
-> 0.0035s
-- transaction_open?()
-> 0.0000s
-- execute("ALTER TABLE security_orchestration_policy_configurations\nADD CONSTRAINT fk_security_policy_configurations_management_project_id\nFOREIGN KEY (security_policy_management_project_id)\nREFERENCES projects (id)\nON DELETE CASCADE\nNOT VALID;\n")
-> 0.0023s
-- execute("SET statement_timeout TO 0")
-> 0.0005s
-- execute("ALTER TABLE security_orchestration_policy_configurations VALIDATE CONSTRAINT fk_security_policy_configurations_management_project_id;")
-> 0.0085s
-- execute("RESET statement_timeout")
-> 0.0008s
-- foreign_keys(:security_orchestration_policy_configurations)
-> 0.0031s
-- remove_foreign_key(:security_orchestration_policy_configurations, {:column=>:security_policy_management_project_id, :on_delete=>:restrict, :name=>"fk_rails_42ed6c25ec"})
-> 0.0044s
== 20210929115340 AddSecurityPolicyConfigurationsManagementProjectIdForeignKey: migrated (0.0455s)
Migrate Down
$ bundle exec rake db:migrate:down VERSION=20210929115340
== 20210929115340 AddSecurityPolicyConfigurationsManagementProjectIdForeignKey: reverting
-- transaction_open?()
-> 0.0000s
-- foreign_keys(:security_orchestration_policy_configurations)
-> 0.0036s
-- transaction_open?()
-> 0.0000s
-- execute("ALTER TABLE security_orchestration_policy_configurations\nADD CONSTRAINT fk_rails_42ed6c25ec\nFOREIGN KEY (security_policy_management_project_id)\nREFERENCES projects (id)\nON DELETE RESTRICT\nNOT VALID;\n")
-> 0.0026s
-- execute("SET statement_timeout TO 0")
-> 0.0005s
-- execute("ALTER TABLE security_orchestration_policy_configurations VALIDATE CONSTRAINT fk_rails_42ed6c25ec;")
-> 0.0109s
-- execute("RESET statement_timeout")
-> 0.0007s
-- foreign_keys(:security_orchestration_policy_configurations)
-> 0.0022s
-- remove_foreign_key(:security_orchestration_policy_configurations, {:column=>:security_policy_management_project_id, :on_delete=>:cascade, :name=>"fk_security_policy_configurations_management_project_id"})
-> 0.0033s
== 20210929115340 AddSecurityPolicyConfigurationsManagementProjectIdForeignKey: reverted (0.0478s)
How to set up and validate locally
- Create a policy following the instructions
- Open the newly created policy project (with name
<project name> - Security policy project
) - Delete the policy project
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.
Edited by Sashi Kumar Kumaresan