-- THE AIM OF THIS FILE IS TO COLLECT SQL COMMANDS TO ADD "ON DELETE CASCADE" CONSTRAINTS
-- SOME OF THE STATEMENTS MAY NOT MAKE SENSE IN THIS BRANCH, AS WE ARE SUPPOSED NOT TO USE THE funding_funding TABLE ANYMORE
 
-- when we delete a funding we want to the delete all the related stuff: subfundings, projects, organisations
alter table funding_funding drop constraint funding_funding_funding1_fkey;
alter table funding_funding add constraint funding_funding_funding1_fkey FOREIGN KEY (funding1) REFERENCES fundings(id) ON DELETE CASCADE;
alter table funding_funding drop constraint funding_funding_funding2_fkey;
alter table funding_funding add constraint funding_funding_funding2_fkey FOREIGN KEY (funding2) REFERENCES fundings(id) ON DELETE CASCADE;

alter table project_funding drop constraint project_funding_funding_fkey;
alter table project_funding add constraint project_funding_funding_fkey FOREIGN KEY (funding) REFERENCES fundings(id) on delete cascade;
alter table project_funding drop constraint project_funding_project_fkey;
alter table project_funding add constraint project_funding_project_fkey FOREIGN KEY (project) REFERENCES projects(id) on delete cascade;

alter table organization_funding drop CONSTRAINT organization_funding_funding_fkey;
alter table organization_funding add CONSTRAINT organization_funding_funding_fkey FOREIGN KEY (funding) REFERENCES fundings(id) on delete cascade;

-- when we delete an organisation we want to delete all the related stuff
ALTER TABLE datasource_organization DROP CONSTRAINT datasource_organization_organization_fkey;
ALTER TABLE datasource_organization ADD CONSTRAINT datasource_organization_organization_fkey FOREIGN KEY (organization) REFERENCES organizations(id) ON DELETE CASCADE;
ALTER TABLE project_organization DROP CONSTRAINT project_organization_resporganization_fkey;
ALTER TABLE project_organization ADD CONSTRAINT project_organization_resporganization_fkey FOREIGN KEY (resporganization) REFERENCES organizations(id) ON DELETE CASCADE;

-- when we delete a project, also its link to organisations must be deleted
ALTER TABLE project_organization DROP CONSTRAINT project_organization_project_fkey;
ALTER TABLE project_organization ADD CONSTRAINT project_organization_project_fkey FOREIGN KEY (project) REFERENCES projects(id) ON DELETE CASCADE;

-- when we delete a fundingpath, also the corresponding entries in the many-to-many tabel to projects must be deleted
ALTER TABLE project_fundingpath DROP CONSTRAINT project_fundingpath_funding_fkey;
ALTER TABLE project_fundingpath ADD CONSTRAINT project_fundingpath_funding_fkey FOREIGN KEY (funding) REFERENCES fundingpaths(id) ON DELETE CASCADE;

-- when we delete a project, also its links to fundingpaths must be deleted
ALTER TABLE project_fundingpath DROP CONSTRAINT project_fundingpath_project_fkey;
ALTER TABLE project_fundingpath ADD CONSTRAINT project_fundingpath_project_fkey FOREIGN KEY (project) REFERENCES projects(id) ON DELETE CASCADE;
