com.castsoftware.aip.datamart/add_foreign_keys.sql at master · CAST-Extend/com.castsoftware.aip.datamart · GitHub

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

----------------
-- ED TABLES
----------------
ALTER TABLE :schema.SRC_MOD_OBJECTS
ADD CONSTRAINT SRC_MOD_OBJECTS_FK     FOREIGN KEY (OBJECT_ID) REFERENCES :schema.SRC_OBJECTS (OBJECT_ID) ON DELETE CASCADE;

ALTER TABLE :schema.SRC_TRX_OBJECTS
ADD CONSTRAINT SRC_TRX_OBJECTS_FK     FOREIGN KEY (TRX_ID) REFERENCES :schema.SRC_TRANSACTIONS (TRX_ID) ON DELETE CASCADE,
ADD CONSTRAINT SRC_TRX_OBJECTS_FK2    FOREIGN KEY (OBJECT_ID) REFERENCES :schema.SRC_OBJECTS (OBJECT_ID) ON DELETE CASCADE;

ALTER TABLE :schema.SRC_TRX_HEALTH_IMPACTS
ADD CONSTRAINT SRC_TRX_HEALTH_IMPACTS_FK     FOREIGN KEY (TRX_ID) REFERENCES :schema.SRC_TRANSACTIONS (TRX_ID) ON DELETE CASCADE,
ADD CONSTRAINT SRC_TRX_HEALTH_IMPACTS_FK2    FOREIGN KEY (OBJECT_ID) REFERENCES :schema.SRC_OBJECTS (OBJECT_ID) ON DELETE CASCADE;

ALTER TABLE :schema.SRC_VIOLATIONS
ADD CONSTRAINT SRC_VIOLATIONS_FK     FOREIGN KEY (SNAPSHOT_ID) REFERENCES :schema.DIM_SNAPSHOTS (SNAPSHOT_ID) ON DELETE CASCADE,
ADD CONSTRAINT SRC_VIOLATIONS_FK2    FOREIGN KEY (RULE_ID) REFERENCES :schema.DIM_RULES (RULE_ID) ON DELETE CASCADE,
ADD CONSTRAINT SRC_VIOLATIONS_FK3    FOREIGN KEY (OBJECT_ID) REFERENCES :schema.SRC_OBJECTS (OBJECT_ID) ON DELETE CASCADE;

ALTER TABLE :schema.SRC_HEALTH_IMPACTS
ADD CONSTRAINT SRC_HEALTH_IMPACTS_FK FOREIGN KEY (OBJECT_ID) REFERENCES :schema.SRC_OBJECTS (OBJECT_ID) ON DELETE CASCADE;

ALTER TABLE :schema.USR_EXCLUSIONS
ADD CONSTRAINT USR_EXCLUSIONS_FK     FOREIGN KEY (RULE_ID) REFERENCES :schema.DIM_RULES (RULE_ID) ON DELETE CASCADE,
ADD CONSTRAINT USR_EXCLUSIONS_FK2    FOREIGN KEY (OBJECT_ID) REFERENCES :schema.SRC_OBJECTS (OBJECT_ID) ON DELETE CASCADE;

ALTER TABLE :schema.USR_ACTION_PLAN
ADD CONSTRAINT USR_ACTION_PLAN_FK    FOREIGN KEY (RULE_ID) REFERENCES :schema.DIM_RULES (RULE_ID) ON DELETE CASCADE,
ADD CONSTRAINT USR_ACTION_PLAN_FK2   FOREIGN KEY (OBJECT_ID) REFERENCES :schema.SRC_OBJECTS (OBJECT_ID) ON DELETE CASCADE;

ALTER TABLE :schema.APP_FINDINGS_MEASURES
ADD CONSTRAINT APP_FINDINGS_MEASURES_FK  FOREIGN KEY (RULE_ID) REFERENCES :schema.DIM_RULES (RULE_ID),
ADD CONSTRAINT APP_FINDINGS_MEASURES_FK  FOREIGN KEY (SNAPSHOT_ID) REFERENCES :schema.DIM_SNAPSHOTS(SNAPSHOT_ID);