
-- Creating primary key constraints

ALTER TABLE DATA ADD CONSTRAINT DATA_PK PRIMARY KEY (ID);
ALTER TABLE DATA_VALUES ADD CONSTRAINT DAVA_PK PRIMARY KEY (ID);
ALTER TABLE EXPERIMENTS ADD CONSTRAINT EXPE_PK PRIMARY KEY (ID);
ALTER TABLE EXPERIMENT_PROPERTIES ADD CONSTRAINT EXPR_PK PRIMARY KEY (ID);
ALTER TABLE EXPERIMENT_TYPES ADD CONSTRAINT EXTY_PK PRIMARY KEY (ID);
ALTER TABLE EXTERNAL_DATA ADD CONSTRAINT EXDA_PK PRIMARY KEY (DATA_ID);
ALTER TABLE FILE_FORMAT_TYPES ADD CONSTRAINT FFTY_PK PRIMARY KEY (ID);
ALTER TABLE LOCATOR_TYPES ADD CONSTRAINT LOTY_PK PRIMARY KEY (ID);
ALTER TABLE MATERIALS ADD CONSTRAINT MATE_PK PRIMARY KEY (ID);
ALTER TABLE MATERIAL_BATCHES ADD CONSTRAINT MABA_PK PRIMARY KEY (ID);
ALTER TABLE MATERIAL_TYPES ADD CONSTRAINT MATY_PK PRIMARY KEY (ID);
ALTER TABLE MOLECULES ADD CONSTRAINT MOLE_PK PRIMARY KEY (MATE_ID);
ALTER TABLE OBSERVABLE_TYPES ADD CONSTRAINT OBTY_PK PRIMARY KEY (ID);
ALTER TABLE ORGANIZATIONS ADD CONSTRAINT ORGA_PK PRIMARY KEY (ID);
ALTER TABLE PERSONS ADD CONSTRAINT PERS_PK PRIMARY KEY (ID);
ALTER TABLE PROCEDURES ADD CONSTRAINT PROC_PK PRIMARY KEY (ID);
ALTER TABLE PROCEDURE_TYPES ADD CONSTRAINT PCTY_PK PRIMARY KEY (ID);
ALTER TABLE PROJECTS ADD CONSTRAINT PROJ_PK PRIMARY KEY (ID);
ALTER TABLE SAMPLES ADD CONSTRAINT SAMP_PK PRIMARY KEY (ID);
ALTER TABLE SAMPLE_COMPONENTS ADD CONSTRAINT SACO_PK PRIMARY KEY (ID);
ALTER TABLE SAMPLE_COMPONENT_MATERIALS ADD CONSTRAINT SCMA_PK PRIMARY KEY (ID);
ALTER TABLE SAMPLE_INPUTS ADD CONSTRAINT SAIN_PK PRIMARY KEY (ID);
ALTER TABLE SAMPLE_TYPES ADD CONSTRAINT SATY_PK PRIMARY KEY (ID);

-- Creating unique constraints

ALTER TABLE DATA_VALUES ADD CONSTRAINT DAVA_BK_UK UNIQUE (DATA_ID, SACO_ID);
ALTER TABLE EXPERIMENTS ADD CONSTRAINT EXPE_BK_UK UNIQUE (CODE, PROJ_ID);
ALTER TABLE EXPERIMENT_PROPERTIES ADD CONSTRAINT EXPR_BK_UK UNIQUE (EXPE_ID, FILE_NAME, VERSION);
ALTER TABLE EXPERIMENT_TYPES ADD CONSTRAINT EXTY_BK_UK UNIQUE (CODE);
ALTER TABLE EXTERNAL_DATA ADD CONSTRAINT EXDA_BK_UK UNIQUE (LOCATION);
ALTER TABLE FILE_FORMAT_TYPES ADD CONSTRAINT FFTY_BK_UK UNIQUE (CODE);
ALTER TABLE LOCATOR_TYPES ADD CONSTRAINT LOTY_BK_UK UNIQUE (CODE);
ALTER TABLE MATERIALS ADD CONSTRAINT MATE_BK_UK UNIQUE (CODE);
ALTER TABLE MATERIAL_TYPES ADD CONSTRAINT MATY_BK_UK UNIQUE (CODE);
ALTER TABLE MATERIAL_TYPES ADD CONSTRAINT MATY_UK UNIQUE (CODE);
ALTER TABLE OBSERVABLE_TYPES ADD CONSTRAINT OBTY_BK_UK UNIQUE (CODE);
ALTER TABLE ORGANIZATIONS ADD CONSTRAINT ORGA_BK_UK UNIQUE (CODE);
ALTER TABLE PERSONS ADD CONSTRAINT PERS_BK_UK UNIQUE (USER_ID);
ALTER TABLE PROCEDURE_TYPES ADD CONSTRAINT PCTY_BK_UK UNIQUE (CODE);
ALTER TABLE PROJECTS ADD CONSTRAINT PROJ_BK_UK UNIQUE (CODE, ORGA_ID);
ALTER TABLE SAMPLES ADD CONSTRAINT SAMP_BK_UK UNIQUE (CODE);
ALTER TABLE SAMPLE_COMPONENTS ADD CONSTRAINT SACO_BK_UK UNIQUE (CODE, SAMP_ID);
ALTER TABLE SAMPLE_COMPONENT_MATERIALS ADD CONSTRAINT SCMA_BK_UK UNIQUE (MABA_ID, SACO_ID);
ALTER TABLE SAMPLE_INPUTS ADD CONSTRAINT SAIN_BK_UK UNIQUE (SAMP_ID, PROC_ID);
ALTER TABLE SAMPLE_TYPES ADD CONSTRAINT SATY_BK_UK UNIQUE (CODE);

-- Creating foreign key constraints

ALTER TABLE DATA ADD CONSTRAINT DATA_OBTY_FK FOREIGN KEY (OBTY_ID) REFERENCES OBSERVABLE_TYPES(ID);
ALTER TABLE DATA ADD CONSTRAINT DATA_PROC_FK FOREIGN KEY (PROC_ID_ACQUIRED_BY) REFERENCES PROCEDURES(ID);
ALTER TABLE DATA ADD CONSTRAINT DATA_SAMP_DERIVED_FROM_FK FOREIGN KEY (SAMP_ID_DERIVED_FROM) REFERENCES SAMPLES(ID);
ALTER TABLE DATA ADD CONSTRAINT DATA_SAMP_FK FOREIGN KEY (SAMP_ID_ACQUIRED_FROM) REFERENCES SAMPLES(ID);
ALTER TABLE DATA_VALUES ADD CONSTRAINT DAVA_DATA_FK FOREIGN KEY (DATA_ID) REFERENCES DATA(ID);
ALTER TABLE DATA_VALUES ADD CONSTRAINT DAVA_SACO_FK FOREIGN KEY (SACO_ID) REFERENCES SAMPLE_COMPONENTS(ID);
ALTER TABLE EXPERIMENTS ADD CONSTRAINT EXPE_EXTY_FK FOREIGN KEY (EXTY_ID) REFERENCES EXPERIMENT_TYPES(ID);
ALTER TABLE EXPERIMENTS ADD CONSTRAINT EXPE_MATE_FK FOREIGN KEY (MATE_ID_STUDY_OBJECT) REFERENCES MATERIALS(ID);
ALTER TABLE EXPERIMENTS ADD CONSTRAINT EXPE_PERS_FK FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID);
ALTER TABLE EXPERIMENTS ADD CONSTRAINT EXPE_PROJ_FK FOREIGN KEY (PROJ_ID) REFERENCES PROJECTS(ID);
ALTER TABLE EXPERIMENT_PROPERTIES ADD CONSTRAINT EXPR_EXPE_FK FOREIGN KEY (EXPE_ID) REFERENCES EXPERIMENTS(ID);
ALTER TABLE EXPERIMENT_PROPERTIES ADD CONSTRAINT EXPR_PERS_FK FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID);
ALTER TABLE EXTERNAL_DATA ADD CONSTRAINT EXDA_DATA_FK FOREIGN KEY (DATA_ID) REFERENCES DATA(ID);
ALTER TABLE EXTERNAL_DATA ADD CONSTRAINT EXDA_FFTY_FK FOREIGN KEY (FFTY_ID) REFERENCES FILE_FORMAT_TYPES(ID);
ALTER TABLE EXTERNAL_DATA ADD CONSTRAINT EXDA_LOTY_FK FOREIGN KEY (LOTY_ID) REFERENCES LOCATOR_TYPES(ID);
ALTER TABLE MATERIALS ADD CONSTRAINT MATE_MATY_FK FOREIGN KEY (MATY_ID) REFERENCES MATERIAL_TYPES(ID);
ALTER TABLE MATERIALS ADD CONSTRAINT MATE_PERS_FK FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID);
ALTER TABLE MATERIAL_BATCHES ADD CONSTRAINT MABA_MATE_FK FOREIGN KEY (MATE_ID) REFERENCES MATERIALS(ID);
ALTER TABLE MATERIAL_BATCHES ADD CONSTRAINT MABA_PERS_FK FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID);
ALTER TABLE MATERIAL_BATCHES ADD CONSTRAINT MABA_PROC_FK FOREIGN KEY (PROC_ID) REFERENCES PROCEDURES(ID);
ALTER TABLE MOLECULES ADD CONSTRAINT MOLE_MATE_FK FOREIGN KEY (MATE_ID) REFERENCES MATERIALS(ID);
ALTER TABLE MOLECULES ADD CONSTRAINT MOLE_MOLE_FK FOREIGN KEY (MOLE_ID_INHIBITOR_OF) REFERENCES MOLECULES(MATE_ID);
ALTER TABLE PROCEDURES ADD CONSTRAINT PROC_EXPE_FK FOREIGN KEY (EXPE_ID) REFERENCES EXPERIMENTS(ID);
ALTER TABLE PROCEDURES ADD CONSTRAINT PROC_PCTY_FK FOREIGN KEY (PCTY_ID) REFERENCES PROCEDURE_TYPES(ID);
ALTER TABLE PROCEDURES ADD CONSTRAINT PROC_PERS_FK FOREIGN KEY (PERS_ID) REFERENCES PERSONS(ID);
ALTER TABLE PROJECTS ADD CONSTRAINT PROJ_ORGA_FK FOREIGN KEY (ORGA_ID) REFERENCES ORGANIZATIONS(ID);
ALTER TABLE SAMPLES ADD CONSTRAINT SAMP_PERS_FK FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID);
ALTER TABLE SAMPLES ADD CONSTRAINT SAMP_PROC_FK FOREIGN KEY (PROC_ID) REFERENCES PROCEDURES(ID);
ALTER TABLE SAMPLES ADD CONSTRAINT SAMP_SAMP_FK_GENERATED_FROM FOREIGN KEY (SAMP_ID_GENERATED_FROM) REFERENCES SAMPLES(ID);
ALTER TABLE SAMPLES ADD CONSTRAINT SAMP_SAMP_FK_TOP FOREIGN KEY (SAMP_ID_TOP) REFERENCES SAMPLES(ID);
ALTER TABLE SAMPLES ADD CONSTRAINT SAMP_SATY_FK FOREIGN KEY (SATY_ID) REFERENCES SAMPLE_TYPES(ID);
ALTER TABLE SAMPLE_COMPONENTS ADD CONSTRAINT SACO_SAMP_FK FOREIGN KEY (SAMP_ID) REFERENCES SAMPLES(ID);
ALTER TABLE SAMPLE_COMPONENT_MATERIALS ADD CONSTRAINT SCMA_MABA_FK FOREIGN KEY (MABA_ID) REFERENCES MATERIAL_BATCHES(ID);
ALTER TABLE SAMPLE_COMPONENT_MATERIALS ADD CONSTRAINT SCMA_SACO_FK FOREIGN KEY (SACO_ID) REFERENCES SAMPLE_COMPONENTS(ID);
ALTER TABLE SAMPLE_INPUTS ADD CONSTRAINT SAIN_PROC_FK FOREIGN KEY (PROC_ID) REFERENCES PROCEDURES(ID);
ALTER TABLE SAMPLE_INPUTS ADD CONSTRAINT SAIN_SAMP_FK FOREIGN KEY (SAMP_ID) REFERENCES SAMPLES(ID);
