-
Notifications
You must be signed in to change notification settings - Fork 24
/
029-foreign-keys-deferrable.sql
87 lines (85 loc) · 4.54 KB
/
029-foreign-keys-deferrable.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
-- https://github.com/scalableminds/webknossos/pull/3328
START TRANSACTION;
ALTER TABLE webknossos.analytics
DROP CONSTRAINT user_ref;
ALTER TABLE webknossos.annotations
DROP CONSTRAINT task_ref,
DROP CONSTRAINT team_ref,
DROP CONSTRAINT user_ref;
ALTER TABLE webknossos.dataSets
DROP CONSTRAINT organization_ref,
DROP CONSTRAINT dataStore_ref;
ALTER TABLE webknossos.dataSet_layers
DROP CONSTRAINT dataSet_ref;
ALTER TABLE webknossos.dataSet_allowedTeams
DROP CONSTRAINT dataSet_ref,
DROP CONSTRAINT team_ref;
ALTER TABLE webknossos.dataSet_resolutions
DROP CONSTRAINT dataSet_ref;
ALTER TABLE webknossos.projects
DROP CONSTRAINT team_ref,
DROP CONSTRAINT user_ref;
ALTER TABLE webknossos.scripts
DROP CONSTRAINT user_ref;
ALTER TABLE webknossos.taskTypes
DROP CONSTRAINT team_ref;
ALTER TABLE webknossos.tasks
DROP CONSTRAINT project_ref,
DROP CONSTRAINT script_ref;
ALTER TABLE webknossos.teams
DROP CONSTRAINT organization_ref;
ALTER TABLE webknossos.timespans
DROP CONSTRAINT user_ref,
DROP CONSTRAINT annotation_ref;
ALTER TABLE webknossos.users
DROP CONSTRAINT organization_ref;
ALTER TABLE webknossos.user_team_roles
DROP CONSTRAINT user_ref,
DROP CONSTRAINT team_ref;
ALTER TABLE webknossos.user_experiences
DROP CONSTRAINT user_ref;
ALTER TABLE webknossos.user_dataSetConfigurations
DROP CONSTRAINT user_ref,
DROP CONSTRAINT dataSet_ref;
ALTER TABLE webknossos.analytics
ADD CONSTRAINT user_ref FOREIGN KEY(_user) REFERENCES webknossos.users(_id) DEFERRABLE;
ALTER TABLE webknossos.annotations
ADD CONSTRAINT task_ref FOREIGN KEY(_task) REFERENCES webknossos.tasks(_id) ON DELETE SET NULL DEFERRABLE,
ADD CONSTRAINT team_ref FOREIGN KEY(_team) REFERENCES webknossos.teams(_id) DEFERRABLE,
ADD CONSTRAINT user_ref FOREIGN KEY(_user) REFERENCES webknossos.users(_id) DEFERRABLE;
ALTER TABLE webknossos.dataSets
ADD CONSTRAINT organization_ref FOREIGN KEY(_organization) REFERENCES webknossos.organizations(_id) DEFERRABLE,
ADD CONSTRAINT dataStore_ref FOREIGN KEY(_dataStore) REFERENCES webknossos.dataStores(name) DEFERRABLE;
ALTER TABLE webknossos.dataSet_layers
ADD CONSTRAINT dataSet_ref FOREIGN KEY(_dataSet) REFERENCES webknossos.dataSets(_id) ON DELETE CASCADE DEFERRABLE;
ALTER TABLE webknossos.dataSet_allowedTeams
ADD CONSTRAINT dataSet_ref FOREIGN KEY(_dataSet) REFERENCES webknossos.dataSets(_id) ON DELETE CASCADE DEFERRABLE,
ADD CONSTRAINT team_ref FOREIGN KEY(_team) REFERENCES webknossos.teams(_id) ON DELETE CASCADE DEFERRABLE;
ALTER TABLE webknossos.dataSet_resolutions
ADD CONSTRAINT dataSet_ref FOREIGN KEY(_dataSet) REFERENCES webknossos.dataSets(_id) ON DELETE CASCADE DEFERRABLE;
ALTER TABLE webknossos.projects
ADD CONSTRAINT team_ref FOREIGN KEY(_team) REFERENCES webknossos.teams(_id) DEFERRABLE,
ADD CONSTRAINT user_ref FOREIGN KEY(_owner) REFERENCES webknossos.users(_id) DEFERRABLE;
ALTER TABLE webknossos.scripts
ADD CONSTRAINT user_ref FOREIGN KEY(_owner) REFERENCES webknossos.users(_id) DEFERRABLE;
ALTER TABLE webknossos.taskTypes
ADD CONSTRAINT team_ref FOREIGN KEY(_team) REFERENCES webknossos.teams(_id) ON DELETE CASCADE DEFERRABLE;
ALTER TABLE webknossos.tasks
ADD CONSTRAINT project_ref FOREIGN KEY(_project) REFERENCES webknossos.projects(_id) DEFERRABLE,
ADD CONSTRAINT script_ref FOREIGN KEY(_script) REFERENCES webknossos.scripts(_id) ON DELETE SET NULL DEFERRABLE;
ALTER TABLE webknossos.teams
ADD CONSTRAINT organization_ref FOREIGN KEY(_organization) REFERENCES webknossos.organizations(_id) DEFERRABLE;
ALTER TABLE webknossos.timespans
ADD CONSTRAINT user_ref FOREIGN KEY(_user) REFERENCES webknossos.users(_id) ON DELETE CASCADE DEFERRABLE,
ADD CONSTRAINT annotation_ref FOREIGN KEY(_annotation) REFERENCES webknossos.annotations(_id) ON DELETE SET NULL DEFERRABLE;
ALTER TABLE webknossos.users
ADD CONSTRAINT organization_ref FOREIGN KEY(_organization) REFERENCES webknossos.organizations(_id) DEFERRABLE;
ALTER TABLE webknossos.user_team_roles
ADD CONSTRAINT user_ref FOREIGN KEY(_user) REFERENCES webknossos.users(_id) ON DELETE CASCADE DEFERRABLE,
ADD CONSTRAINT team_ref FOREIGN KEY(_team) REFERENCES webknossos.teams(_id) ON DELETE CASCADE DEFERRABLE;
ALTER TABLE webknossos.user_experiences
ADD CONSTRAINT user_ref FOREIGN KEY(_user) REFERENCES webknossos.users(_id) ON DELETE CASCADE DEFERRABLE;
ALTER TABLE webknossos.user_dataSetConfigurations
ADD CONSTRAINT user_ref FOREIGN KEY(_user) REFERENCES webknossos.users(_id) ON DELETE CASCADE DEFERRABLE,
ADD CONSTRAINT dataSet_ref FOREIGN KEY(_dataSet) REFERENCES webknossos.dataSets(_id) ON DELETE CASCADE DEFERRABLE;
COMMIT TRANSACTION;