1
|
-- THE AIM OF THIS FILE IS TO COLLECT SQL COMMANDS TO ADD "ON DELETE CASCADE" CONSTRAINTS
|
2
|
-- SOME OF THE STATEMENTS MAY NOT MAKE SENSE IN THIS BRANCH, AS WE ARE SUPPOSED NOT TO USE THE funding_funding TABLE ANYMORE
|
3
|
|
4
|
-- when we delete a funding we want to the delete all the related stuff: subfundings, projects, organisations
|
5
|
ALTER TABLE funding_funding
|
6
|
DROP CONSTRAINT funding_funding_funding1_fkey;
|
7
|
ALTER TABLE funding_funding
|
8
|
ADD CONSTRAINT funding_funding_funding1_fkey FOREIGN KEY (funding1) REFERENCES fundings (id) ON DELETE CASCADE;
|
9
|
ALTER TABLE funding_funding
|
10
|
DROP CONSTRAINT funding_funding_funding2_fkey;
|
11
|
ALTER TABLE funding_funding
|
12
|
ADD CONSTRAINT funding_funding_funding2_fkey FOREIGN KEY (funding2) REFERENCES fundings (id) ON DELETE CASCADE;
|
13
|
|
14
|
ALTER TABLE project_funding
|
15
|
DROP CONSTRAINT project_funding_funding_fkey;
|
16
|
ALTER TABLE project_funding
|
17
|
ADD CONSTRAINT project_funding_funding_fkey FOREIGN KEY (funding) REFERENCES fundings (id) ON DELETE CASCADE;
|
18
|
ALTER TABLE project_funding
|
19
|
DROP CONSTRAINT project_funding_project_fkey;
|
20
|
ALTER TABLE project_funding
|
21
|
ADD CONSTRAINT project_funding_project_fkey FOREIGN KEY (project) REFERENCES projects (id) ON DELETE CASCADE;
|
22
|
|
23
|
ALTER TABLE organization_funding
|
24
|
DROP CONSTRAINT organization_funding_funding_fkey;
|
25
|
ALTER TABLE organization_funding
|
26
|
ADD CONSTRAINT organization_funding_funding_fkey FOREIGN KEY (funding) REFERENCES fundings (id) ON DELETE CASCADE;
|
27
|
|
28
|
-- when we delete an organisation we want to delete all the related stuff
|
29
|
ALTER TABLE datasource_organization
|
30
|
DROP CONSTRAINT datasource_organization_organization_fkey;
|
31
|
ALTER TABLE datasource_organization
|
32
|
ADD CONSTRAINT datasource_organization_organization_fkey FOREIGN KEY (organization) REFERENCES organizations (id) ON DELETE CASCADE;
|
33
|
ALTER TABLE project_organization
|
34
|
DROP CONSTRAINT project_organization_resporganization_fkey;
|
35
|
ALTER TABLE project_organization
|
36
|
ADD CONSTRAINT project_organization_resporganization_fkey FOREIGN KEY (resporganization) REFERENCES organizations (id) ON DELETE CASCADE;
|
37
|
|
38
|
-- when we delete a project, also its link to organisations must be deleted
|
39
|
ALTER TABLE project_organization
|
40
|
DROP CONSTRAINT project_organization_project_fkey;
|
41
|
ALTER TABLE project_organization
|
42
|
ADD CONSTRAINT project_organization_project_fkey FOREIGN KEY (project) REFERENCES projects (id) ON DELETE CASCADE;
|
43
|
|
44
|
-- when we delete a fundingpath, also the corresponding entries in the many-to-many tabel to projects must be deleted
|
45
|
ALTER TABLE project_fundingpath
|
46
|
DROP CONSTRAINT project_fundingpath_funding_fkey;
|
47
|
ALTER TABLE project_fundingpath
|
48
|
ADD CONSTRAINT project_fundingpath_funding_fkey FOREIGN KEY (funding) REFERENCES fundingpaths (id) ON DELETE CASCADE;
|
49
|
|
50
|
-- when we delete a project, also its links to fundingpaths must be deleted
|
51
|
ALTER TABLE project_fundingpath
|
52
|
DROP CONSTRAINT project_fundingpath_project_fkey;
|
53
|
ALTER TABLE project_fundingpath
|
54
|
ADD CONSTRAINT project_fundingpath_project_fkey FOREIGN KEY (project) REFERENCES projects (id) ON DELETE CASCADE;
|