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

1. Create a policy following the instructions
2. Open the newly created policy project (with name <project name> - Security policy project)
3. 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.