Revision 48139
Added by Alessia Bardi over 6 years ago
patch-constraints.sql | ||
---|---|---|
1 | 1 |
-- THE AIM OF THIS FILE IS TO COLLECT SQL COMMANDS TO ADD "ON DELETE CASCADE" CONSTRAINTS |
2 | 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 |
|
|
3 |
|
|
4 | 4 |
-- when we delete a funding we want to the delete all the related stuff: subfundings, projects, organisations |
5 |
alter table funding_funding drop constraint funding_funding_funding1_fkey; |
|
6 |
alter table funding_funding add constraint funding_funding_funding1_fkey FOREIGN KEY (funding1) REFERENCES fundings(id) ON DELETE CASCADE; |
|
7 |
alter table funding_funding drop constraint funding_funding_funding2_fkey; |
|
8 |
alter table funding_funding add constraint funding_funding_funding2_fkey FOREIGN KEY (funding2) REFERENCES fundings(id) ON DELETE CASCADE; |
|
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; |
|
9 | 13 |
|
10 |
alter table project_funding drop constraint project_funding_funding_fkey; |
|
11 |
alter table project_funding add constraint project_funding_funding_fkey FOREIGN KEY (funding) REFERENCES fundings(id) on delete cascade; |
|
12 |
alter table project_funding drop constraint project_funding_project_fkey; |
|
13 |
alter table project_funding add constraint project_funding_project_fkey FOREIGN KEY (project) REFERENCES projects(id) on delete cascade; |
|
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; |
|
14 | 22 |
|
15 |
alter table organization_funding drop CONSTRAINT organization_funding_funding_fkey; |
|
16 |
alter table organization_funding add CONSTRAINT organization_funding_funding_fkey FOREIGN KEY (funding) REFERENCES fundings(id) on delete cascade; |
|
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; |
|
17 | 27 |
|
18 | 28 |
-- when we delete an organisation we want to delete all the related stuff |
19 |
ALTER TABLE datasource_organization DROP CONSTRAINT datasource_organization_organization_fkey; |
|
20 |
ALTER TABLE datasource_organization ADD CONSTRAINT datasource_organization_organization_fkey FOREIGN KEY (organization) REFERENCES organizations(id) ON DELETE CASCADE; |
|
21 |
ALTER TABLE project_organization DROP CONSTRAINT project_organization_resporganization_fkey; |
|
22 |
ALTER TABLE project_organization ADD CONSTRAINT project_organization_resporganization_fkey FOREIGN KEY (resporganization) REFERENCES organizations(id) ON DELETE CASCADE; |
|
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; |
|
23 | 37 |
|
24 | 38 |
-- when we delete a project, also its link to organisations must be deleted |
25 |
ALTER TABLE project_organization DROP CONSTRAINT project_organization_project_fkey; |
|
26 |
ALTER TABLE project_organization ADD CONSTRAINT project_organization_project_fkey FOREIGN KEY (project) REFERENCES projects(id) ON DELETE CASCADE; |
|
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; |
|
27 | 43 |
|
28 | 44 |
-- when we delete a fundingpath, also the corresponding entries in the many-to-many tabel to projects must be deleted |
29 |
ALTER TABLE project_fundingpath DROP CONSTRAINT project_fundingpath_funding_fkey; |
|
30 |
ALTER TABLE project_fundingpath ADD CONSTRAINT project_fundingpath_funding_fkey FOREIGN KEY (funding) REFERENCES fundingpaths(id) ON DELETE CASCADE; |
|
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; |
|
31 | 49 |
|
32 | 50 |
-- when we delete a project, also its links to fundingpaths must be deleted |
33 |
ALTER TABLE project_fundingpath DROP CONSTRAINT project_fundingpath_project_fkey; |
|
34 |
ALTER TABLE project_fundingpath ADD CONSTRAINT project_fundingpath_project_fkey FOREIGN KEY (project) REFERENCES projects(id) ON DELETE CASCADE; |
|
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; |
Also available in: Unified diff
integrated (hopefully) all required changes from dnet40