-- 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;