i2b2_constraints.sql

-- i2b2 Metadata Schema
alter table i2b2metadata.i2b2 add constraint i2b2_c_fullname_uq unique (c_fullname); --could be pk. domain object refers to fullName+name as composite key
alter table i2b2metadata.i2b2_secure add constraint i2b2_secure_c_fullname_uq unique (c_fullname); --could be pk. domain object refers to fullName+name as composite key
-- Delete secure concepts that are not present in i2b2 table
--delete from i2b2metadata.i2b2_secure isc where not exists(select * from i2b2metadata.i2b2 where c_fullname = isc.c_fullname);
alter table i2b2metadata.i2b2_secure add constraint i2b2_secure_c_fullname_fk foreign key (c_fullname) references i2b2(c_fullname) on delete cascade;
alter table i2b2metadata.i2b2_tags add constraint i2b2_tags_pk primary key (tag_id);
alter table i2b2metadata.i2b2_tags add constraint i2b2_tags_path_fk foreign key (path) references i2b2(c_fullname) on delete cascade;

-- i2b2 Data Schema
alter table i2b2demodata.concept_dimension add constraint concept_dimension_concept_cd_uq unique (concept_cd);
--Delete "security" nodes that are not used anymore. Do not to fix i2b2_create_security_for_trial SP respectively.
--delete from i2b2demodata.observation_fact where concept_cd = 'SECURITY';
alter table i2b2demodata.observation_fact add constraint observation_fact_concept_cd_fk foreign key (concept_cd) references concept_dimension(concept_cd) on delete cascade;
alter table i2b2demodata.observation_fact add constraint observation_fact_patient_num_fk foreign key (patient_num) references patient_dimension(patient_num) on delete cascade;
insert into modifier_dimension(modifier_path, modifier_cd) values ('/@', '@');
alter table i2b2demodata.modifier_dimension add constraint modifier_dimension_modifier_cd_uq unique (modifier_cd);
alter table i2b2demodata.observation_fact add constraint observation_fact_modifier_cd_fk foreign key (modifier_cd) references modifier_dimension(modifier_cd) on delete cascade;

alter table i2b2demodata.patient_trial add constraint patient_trial_pk primary key (patient_num, trial);
alter table i2b2demodata.patient_trial add constraint patient_trial_patient_num_fk foreign key (patient_num) references patient_dimension(patient_num) on delete cascade;

alter table i2b2demodata.modifier_metadata add constraint modifier_metadata_pk primary key (modifier_cd);
alter table i2b2demodata.modifier_metadata add constraint modifier_metadata_modifier_cd_fk foreign key (modifier_cd) references modifier_dimension(modifier_cd) on delete cascade;

alter table i2b2demodata.concept_counts add constraint concept_counts_pk primary key (concept_path);
alter table i2b2demodata.concept_counts add constraint concept_counts_concept_path_fk foreign key (concept_path) references concept_dimension(concept_path) on delete cascade;
--TODO We would need to change ETL scripts to insert records for all parent concepts.
--insert into i2b2demodata.concept_dimension(concept_cd, concept_path) values ('public', '\Public Studies\');
--insert into i2b2demodata.concept_counts(concept_path) values ('\Public Studies\');
--insert into i2b2demodata.concept_dimension(concept_cd, concept_path) values ('public', '\Private Studies\');
--insert into i2b2demodata.concept_counts(concept_path) values ('\Private Studies\');
--alter table i2b2demodata.concept_counts add constraint concept_counts_parent_concept_path_fk foreign key (parent_concept_path) references concept_counts(concept_path) on delete cascade;