-- D:\DDL\postgresql\schema-014.sql
--
-- Generated for ANSI SQL92 on Fri Jan 18  14:50:56 2008 by Server Generator 10.1.2.6.18
------------------------------------------------------------------------------------
--
--  Post-Generation Modifications:
--
--  1. Changed domain FILE from BIT(32000) to BYTEA
--  2. Changed domain TECH_ID from NUMERIC(20) to BIGINT
--  3. Changed domain BOOLEAN_CHAR from CHAR(1) DEFAULT F to BOOLEAN DEFAULT FALSE
--  4. Removed the check constraint AVCON_1197023825_IS_MA_000 CHECK from the
--     table MATERIAL_TYPE_PROPERTY_TYPES as it is only needed in Oracle
--  5. Added the ON DELETE CASCADE qualifier to the foreign key MAPR_MTPT_FK of
--     the MATERIAL_PROPERTIES table
--  6. Added the Sequence and Index sections
------------------------------------------------------------------------------------

-- Creating domains

CREATE DOMAIN BOOLEAN_CHAR AS BOOLEAN DEFAULT FALSE;
CREATE DOMAIN CODE AS VARCHAR(20);
CREATE DOMAIN COLUMN_LABEL AS VARCHAR(40);
CREATE DOMAIN DESCRIPTION_250 AS VARCHAR(250);
CREATE DOMAIN DESCRIPTION_80 AS VARCHAR(80);
CREATE DOMAIN DOUBLE_PRECISION_VALUE AS DOUBLE PRECISION;
CREATE DOMAIN FILE AS BYTEA;
CREATE DOMAIN FILE_NAME AS VARCHAR(100);
CREATE DOMAIN GENERIC_VALUE AS VARCHAR(1024);
CREATE DOMAIN OBJECT_NAME AS VARCHAR(50);
CREATE DOMAIN REAL_VALUE AS REAL;
CREATE DOMAIN TECH_ID AS BIGINT;
CREATE DOMAIN TIME_STAMP AS TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
CREATE DOMAIN USER_ID AS VARCHAR(20);

-- Creating tables

CREATE TABLE CONTROLLED_VOCABULARIES (ID TECH_ID NOT NULL,CODE CODE NOT NULL,DESCRIPTION DESCRIPTION_80,REGISTRATION_TIMESTAMP TIME_STAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,PERS_ID TECH_ID NOT NULL);
CREATE TABLE CONTROLLED_VOCABULARY_TERMS (ID TECH_ID NOT NULL,CODE OBJECT_NAME NOT NULL,REGISTRATION_TIMESTAMP TIME_STAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,COVO_ID TECH_ID NOT NULL,PERS_ID TECH_ID NOT NULL);
CREATE TABLE DATA (ID TECH_ID NOT NULL,REGISTRATION_TIMESTAMP TIME_STAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,OBTY_ID TECH_ID NOT NULL,PROC_ID_ACQUIRED_BY TECH_ID NOT NULL,SAMP_ID_ACQUIRED_FROM TECH_ID,SAMP_ID_DERIVED_FROM TECH_ID);
CREATE TABLE DATA_TYPES (ID TECH_ID NOT NULL,CODE CODE NOT NULL,DESCRIPTION DESCRIPTION_80 NOT NULL);
CREATE TABLE DATA_VALUES (ID TECH_ID NOT NULL,DATA_ID TECH_ID NOT NULL,SACO_ID TECH_ID NOT NULL,VALUE DOUBLE_PRECISION_VALUE NOT NULL);
CREATE TABLE EXPERIMENTS (ID TECH_ID NOT NULL,CODE CODE NOT NULL,EXTY_ID TECH_ID NOT NULL,MATE_ID_STUDY_OBJECT TECH_ID,PERS_ID_REGISTERER TECH_ID,REGISTRATION_TIMESTAMP TIME_STAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,DESCRIPTION DESCRIPTION_250,PROJ_ID TECH_ID NOT NULL,INVA_ID TECH_ID);
CREATE TABLE EXPERIMENT_ATTACHMENTS (ID TECH_ID NOT NULL,EXPE_ID TECH_ID NOT NULL,FILE_NAME FILE_NAME NOT NULL,REGISTRATION_TIMESTAMP TIME_STAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,VALUE FILE NOT NULL,VERSION INTEGER NOT NULL,PERS_ID_REGISTERER TECH_ID NOT NULL);
CREATE TABLE EXPERIMENT_TYPES (ID TECH_ID NOT NULL,CODE CODE NOT NULL,DESCRIPTION DESCRIPTION_80);
CREATE TABLE EXTERNAL_DATA (DATA_ID TECH_ID NOT NULL,LOCATION VARCHAR(200) NOT NULL,LOTY_ID TECH_ID NOT NULL,FFTY_ID TECH_ID NOT NULL);
CREATE TABLE FILE_FORMAT_TYPES (ID TECH_ID NOT NULL,CODE CODE NOT NULL,DESCRIPTION DESCRIPTION_80);
CREATE TABLE INVALIDATIONS (ID TECH_ID NOT NULL,PERS_ID_REGISTERER TECH_ID NOT NULL,REGISTRATION_TIMESTAMP TIME_STAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,REASON DESCRIPTION_250);
CREATE TABLE LOCATOR_TYPES (ID TECH_ID NOT NULL,CODE CODE NOT NULL,DESCRIPTION DESCRIPTION_80);
CREATE TABLE MATERIALS (ID TECH_ID NOT NULL,CODE CODE NOT NULL,MATY_ID TECH_ID NOT NULL,REGISTRATION_TIMESTAMP TIME_STAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,PERS_ID_REGISTERER TECH_ID NOT NULL,MATE_ID_INHIBITOR_OF TECH_ID);
CREATE TABLE MATERIAL_BATCHES (ID TECH_ID NOT NULL,CODE CODE NOT NULL,AMOUNT REAL_VALUE,MATE_ID TECH_ID NOT NULL,PROC_ID TECH_ID,REGISTRATION_TIMESTAMP TIME_STAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,PERS_ID_REGISTERER TECH_ID NOT NULL);
CREATE TABLE MATERIAL_PROPERTIES (ID TECH_ID NOT NULL,MATE_ID TECH_ID NOT NULL,MTPT_ID TECH_ID NOT NULL,VALUE GENERIC_VALUE,REGISTRATION_TIMESTAMP TIME_STAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,PERS_ID_REGISTERER TECH_ID NOT NULL,CVTE_ID TECH_ID);
CREATE TABLE MATERIAL_TYPES (ID TECH_ID NOT NULL,CODE CODE NOT NULL,DESCRIPTION DESCRIPTION_80);
CREATE TABLE MATERIAL_TYPE_PROPERTY_TYPES (ID TECH_ID NOT NULL,MATY_ID TECH_ID NOT NULL,PRTY_ID TECH_ID NOT NULL,IS_MANDATORY BOOLEAN_CHAR NOT NULL DEFAULT 'F',REGISTRATION_TIMESTAMP TIME_STAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,PERS_ID_REGISTERER TECH_ID NOT NULL);
CREATE TABLE OBSERVABLE_TYPES (ID TECH_ID NOT NULL,CODE CODE NOT NULL,DESCRIPTION DESCRIPTION_80);
CREATE TABLE ORGANIZATIONS (ID TECH_ID NOT NULL,CODE CODE NOT NULL);
CREATE TABLE PERSONS (ID TECH_ID NOT NULL,FIRST_NAME VARCHAR(30),LAST_NAME VARCHAR(30),USER_ID USER_ID,EMAIL OBJECT_NAME);
CREATE TABLE PROCEDURES (ID TECH_ID NOT NULL,REGISTRATION_TIMESTAMP TIME_STAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,EXPE_ID TECH_ID NOT NULL,PCTY_ID TECH_ID NOT NULL,PERS_ID TECH_ID);
CREATE TABLE PROCEDURE_TYPES (ID TECH_ID NOT NULL,CODE CODE NOT NULL,DESCRIPTION DESCRIPTION_80 NOT NULL);
CREATE TABLE PROJECTS (ID TECH_ID NOT NULL,CODE CODE NOT NULL,ORGA_ID TECH_ID NOT NULL);
CREATE TABLE PROPERTY_TYPES (ID TECH_ID NOT NULL,CODE CODE NOT NULL,DESCRIPTION DESCRIPTION_80 NOT NULL,LABEL COLUMN_LABEL NOT NULL,DATY_ID TECH_ID NOT NULL,REGISTRATION_TIMESTAMP TIME_STAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,PERS_ID_REGISTERER TECH_ID NOT NULL,COVO_ID TECH_ID);
CREATE TABLE SAMPLES (ID TECH_ID NOT NULL,CODE CODE NOT NULL,SAMP_ID_TOP TECH_ID,SAMP_ID_GENERATED_FROM TECH_ID,SATY_ID TECH_ID NOT NULL,REGISTRATION_TIMESTAMP TIME_STAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,PERS_ID_REGISTERER TECH_ID NOT NULL,INVA_ID TECH_ID,SAMP_ID_CONTROL_LAYOUT TECH_ID);
CREATE TABLE SAMPLE_COMPONENTS (ID TECH_ID NOT NULL,CODE CODE NOT NULL,SAMP_ID TECH_ID NOT NULL);
CREATE TABLE SAMPLE_COMPONENT_MATERIALS (ID TECH_ID NOT NULL,SACO_ID TECH_ID NOT NULL,MABA_ID TECH_ID NOT NULL);
CREATE TABLE SAMPLE_INPUTS (ID TECH_ID NOT NULL,SAMP_ID TECH_ID NOT NULL,PROC_ID TECH_ID NOT NULL);
CREATE TABLE SAMPLE_TYPES (ID TECH_ID NOT NULL,CODE CODE NOT NULL,DESCRIPTION DESCRIPTION_80);

-- Creating sequences

CREATE SEQUENCE CONTROLLED_VOCABULARY_ID_SEQ;
CREATE SEQUENCE CVTE_ID_SEQ;
CREATE SEQUENCE DATA_ID_SEQ;
CREATE SEQUENCE DATA_TYPE_ID_SEQ;
CREATE SEQUENCE DATA_VALUE_ID_SEQ;
CREATE SEQUENCE EXPERIMENT_ATTACHMENT_ID_SEQ;
CREATE SEQUENCE EXPERIMENT_ID_SEQ;
CREATE SEQUENCE EXPERIMENT_TYPE_ID_SEQ;
CREATE SEQUENCE FILE_FORMAT_TYPE_ID_SEQ;
CREATE SEQUENCE INVALIDATION_ID_SEQ;
CREATE SEQUENCE LOCATOR_TYPE_ID_SEQ;
CREATE SEQUENCE MATERIAL_BATCH_ID_SEQ;
CREATE SEQUENCE MATERIAL_ID_SEQ;
CREATE SEQUENCE MATERIAL_PROPERTY_ID_SEQ;
CREATE SEQUENCE MATERIAL_TYPE_ID_SEQ;
CREATE SEQUENCE MTPT_ID_SEQ;
CREATE SEQUENCE OBSERVABLE_TYPE_ID_SEQ;
CREATE SEQUENCE ORGANIZATION_ID_SEQ;
CREATE SEQUENCE PERSON_ID_SEQ;
CREATE SEQUENCE PROCEDURE_ID_SEQ;
CREATE SEQUENCE PROCEDURE_TYPE_ID_SEQ;
CREATE SEQUENCE PROJECT_ID_SEQ;
CREATE SEQUENCE PROPERTY_TYPE_ID_SEQ;
CREATE SEQUENCE SAMPLE_COMPONENT_ID_SEQ;
CREATE SEQUENCE SAMPLE_ID_SEQ;
CREATE SEQUENCE SAMPLE_INPUT_ID_SEQ;
CREATE SEQUENCE SAMPLE_TYPE_ID_SEQ;
CREATE SEQUENCE SCMA_ID_SEQ;

-- Creating primary key constraints

ALTER TABLE CONTROLLED_VOCABULARIES ADD CONSTRAINT COVO_PK PRIMARY KEY(ID);
ALTER TABLE CONTROLLED_VOCABULARY_TERMS ADD CONSTRAINT CVTE_PK PRIMARY KEY(ID);
ALTER TABLE DATA ADD CONSTRAINT DATA_PK PRIMARY KEY(ID);
ALTER TABLE DATA_TYPES ADD CONSTRAINT DATY_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_ATTACHMENTS ADD CONSTRAINT EXAT_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 INVALIDATIONS ADD CONSTRAINT INVA_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_PROPERTIES ADD CONSTRAINT MAPR_PK PRIMARY KEY(ID);
ALTER TABLE MATERIAL_TYPES ADD CONSTRAINT MATY_PK PRIMARY KEY(ID);
ALTER TABLE MATERIAL_TYPE_PROPERTY_TYPES ADD CONSTRAINT MTPT_PK PRIMARY KEY(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 PROPERTY_TYPES ADD CONSTRAINT PRTY_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 CONTROLLED_VOCABULARIES ADD CONSTRAINT COVO_BK_UK UNIQUE(CODE);
ALTER TABLE CONTROLLED_VOCABULARY_TERMS ADD CONSTRAINT CVTE_BK_UK UNIQUE(CODE,COVO_ID);
ALTER TABLE DATA_TYPES ADD CONSTRAINT DATY_BK_UK UNIQUE(CODE);
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_ATTACHMENTS ADD CONSTRAINT EXAT_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,MATY_ID);
ALTER TABLE MATERIAL_BATCHES ADD CONSTRAINT MABA_BK_UK UNIQUE(CODE,MATE_ID);
ALTER TABLE MATERIAL_PROPERTIES ADD CONSTRAINT MAPR_BK_UK UNIQUE(MATE_ID,MTPT_ID);
ALTER TABLE MATERIAL_TYPES ADD CONSTRAINT MATY_BK_UK UNIQUE(CODE);
ALTER TABLE MATERIAL_TYPE_PROPERTY_TYPES ADD CONSTRAINT MTPT_BK_UK UNIQUE(MATY_ID,PRTY_ID);
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 PROPERTY_TYPES ADD CONSTRAINT PRTY_BK_UK UNIQUE(CODE);
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 CONTROLLED_VOCABULARIES ADD CONSTRAINT COVO_PERS_FK FOREIGN KEY (PERS_ID) REFERENCES PERSONS(ID);
ALTER TABLE CONTROLLED_VOCABULARY_TERMS ADD CONSTRAINT CVTE_COVO_FK FOREIGN KEY (COVO_ID) REFERENCES CONTROLLED_VOCABULARIES(ID);
ALTER TABLE CONTROLLED_VOCABULARY_TERMS ADD CONSTRAINT CVTE_PERS_FK FOREIGN KEY (PERS_ID) REFERENCES PERSONS(ID);
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_INVA_FK FOREIGN KEY (INVA_ID) REFERENCES INVALIDATIONS(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_ATTACHMENTS ADD CONSTRAINT EXAT_EXPE_FK FOREIGN KEY (EXPE_ID) REFERENCES EXPERIMENTS(ID);
ALTER TABLE EXPERIMENT_ATTACHMENTS ADD CONSTRAINT EXAT_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 INVALIDATIONS ADD CONSTRAINT INVA_PERS_FK FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID);
ALTER TABLE MATERIALS ADD CONSTRAINT MATE_MATE_FK FOREIGN KEY (MATE_ID_INHIBITOR_OF) REFERENCES MATERIALS(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 MATERIAL_PROPERTIES ADD CONSTRAINT MAPR_CVTE_FK FOREIGN KEY (CVTE_ID) REFERENCES CONTROLLED_VOCABULARY_TERMS(ID);
ALTER TABLE MATERIAL_PROPERTIES ADD CONSTRAINT MAPR_MATE_FK FOREIGN KEY (MATE_ID) REFERENCES MATERIALS(ID);
ALTER TABLE MATERIAL_PROPERTIES ADD CONSTRAINT MAPR_MTPT_FK FOREIGN KEY (MTPT_ID) REFERENCES MATERIAL_TYPE_PROPERTY_TYPES(ID) ON DELETE CASCADE;
ALTER TABLE MATERIAL_PROPERTIES ADD CONSTRAINT MAPR_PERS_FK FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID);
ALTER TABLE MATERIAL_TYPE_PROPERTY_TYPES ADD CONSTRAINT MTPT_MATY_FK FOREIGN KEY (MATY_ID) REFERENCES MATERIAL_TYPES(ID);
ALTER TABLE MATERIAL_TYPE_PROPERTY_TYPES ADD CONSTRAINT MTPT_PERS_FK FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID);
ALTER TABLE MATERIAL_TYPE_PROPERTY_TYPES ADD CONSTRAINT MTPT_PRTY_FK FOREIGN KEY (PRTY_ID) REFERENCES PROPERTY_TYPES(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 PROPERTY_TYPES ADD CONSTRAINT PRTY_COVO_FK FOREIGN KEY (COVO_ID) REFERENCES CONTROLLED_VOCABULARIES(ID);
ALTER TABLE PROPERTY_TYPES ADD CONSTRAINT PRTY_DATY_FK FOREIGN KEY (DATY_ID) REFERENCES DATA_TYPES(ID);
ALTER TABLE PROPERTY_TYPES ADD CONSTRAINT PRTY_PERS_FK FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID);
ALTER TABLE SAMPLES ADD CONSTRAINT SAMP_INVA_FK FOREIGN KEY (INVA_ID) REFERENCES INVALIDATIONS(ID);
ALTER TABLE SAMPLES ADD CONSTRAINT SAMP_PERS_FK FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID);
ALTER TABLE SAMPLES ADD CONSTRAINT SAMP_SAMP_FK_CONTROL_LAYOUT FOREIGN KEY (SAMP_ID_CONTROL_LAYOUT) REFERENCES SAMPLES(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);

-- Creating check constraints

ALTER TABLE DATA ADD CONSTRAINT DATA_SAMP_ARC_CK CHECK ((SAMP_ID_ACQUIRED_FROM IS NOT NULL AND SAMP_ID_DERIVED_FROM IS NULL) OR (SAMP_ID_ACQUIRED_FROM IS NULL AND SAMP_ID_DERIVED_FROM IS NOT NULL));
ALTER TABLE MATERIAL_PROPERTIES ADD CONSTRAINT VALUE_OR_CVTE_ID CHECK ((value IS NOT NULL and cvte_id IS NULL) or (value IS NULL and cvte_id IS NOT NULL));

-- Creating indexes

CREATE INDEX COVO_PERS_FK_I ON CONTROLLED_VOCABULARIES (PERS_ID);
CREATE INDEX CVTE_COVO_FK_I ON CONTROLLED_VOCABULARY_TERMS (COVO_ID);
CREATE INDEX CVTE_PERS_FK_I ON CONTROLLED_VOCABULARY_TERMS (PERS_ID);
CREATE INDEX DATA_OBTY_FK_I ON DATA (OBTY_ID);
CREATE INDEX DATA_PROC_FK_I ON DATA (PROC_ID_ACQUIRED_BY);
CREATE INDEX DATA_SAMP_DERIVED_FROM_FK_I ON DATA (SAMP_ID_DERIVED_FROM);
CREATE INDEX DATA_SAMP_FK_I ON DATA (SAMP_ID_ACQUIRED_FROM);
CREATE INDEX DAVA_DATA_FK_I ON DATA_VALUES (DATA_ID);
CREATE INDEX DAVA_SACO_FK_I ON DATA_VALUES (SACO_ID);
CREATE INDEX EXAT_EXPE_FK_I ON EXPERIMENT_ATTACHMENTS (EXPE_ID);
CREATE INDEX EXAT_PERS_FK_I ON EXPERIMENT_ATTACHMENTS (PERS_ID_REGISTERER);
CREATE INDEX EXDA_FFTY_FK_I ON EXTERNAL_DATA (FFTY_ID);
CREATE INDEX EXDA_LOTY_FK_I ON EXTERNAL_DATA (LOTY_ID);
CREATE INDEX EXPE_EXTY_FK_I ON EXPERIMENTS (EXTY_ID);
CREATE INDEX EXPE_INVA_FK_I ON EXPERIMENTS (INVA_ID);
CREATE INDEX EXPE_MATE_FK_I ON EXPERIMENTS (MATE_ID_STUDY_OBJECT);
CREATE INDEX EXPE_PERS_FK_I ON EXPERIMENTS (PERS_ID_REGISTERER);
CREATE INDEX EXPE_PROJ_FK_I ON EXPERIMENTS (PROJ_ID);
CREATE INDEX INVA_PERS_FK_I ON INVALIDATIONS (PERS_ID_REGISTERER);
CREATE INDEX MABA_MATE_FK_I ON MATERIAL_BATCHES (MATE_ID);
CREATE INDEX MABA_PERS_FK_I ON MATERIAL_BATCHES (PERS_ID_REGISTERER);
CREATE INDEX MABA_PROC_FK_I ON MATERIAL_BATCHES (PROC_ID);
CREATE INDEX MAPR_CVTE_FK_I ON MATERIAL_PROPERTIES (CVTE_ID);
CREATE INDEX MAPR_MATE_FK_I ON MATERIAL_PROPERTIES (MATE_ID);
CREATE INDEX MAPR_MTPT_FK_I ON MATERIAL_PROPERTIES (MTPT_ID);
CREATE INDEX MAPR_PERS_FK_I ON MATERIAL_PROPERTIES (PERS_ID_REGISTERER);
CREATE INDEX MATE_MATE_FK_I ON MATERIALS (MATE_ID_INHIBITOR_OF);
CREATE INDEX MATE_MATY_FK_I ON MATERIALS (MATY_ID);
CREATE INDEX MATE_PERS_FK_I ON MATERIALS (PERS_ID_REGISTERER);
CREATE INDEX MTPT_MATY_FK_I ON MATERIAL_TYPE_PROPERTY_TYPES (MATY_ID);
CREATE INDEX MTPT_PERS_FK_I ON MATERIAL_TYPE_PROPERTY_TYPES (PERS_ID_REGISTERER);
CREATE INDEX MTPT_PRTY_FK_I ON MATERIAL_TYPE_PROPERTY_TYPES (PRTY_ID);
CREATE INDEX PROC_EXPE_FK_I ON PROCEDURES (EXPE_ID);
CREATE INDEX PROC_PCTY_FK_I ON PROCEDURES (PCTY_ID);
CREATE INDEX PROC_PERS_FK_I ON PROCEDURES (PERS_ID);
CREATE INDEX PROJ_ORGA_FK_I ON PROJECTS (ORGA_ID);
CREATE INDEX PRTY_COVO_FK_I ON PROPERTY_TYPES (COVO_ID);
CREATE INDEX PRTY_DATY_FK_I ON PROPERTY_TYPES (DATY_ID);
CREATE INDEX PRTY_PERS_FK_I ON PROPERTY_TYPES (PERS_ID_REGISTERER);
CREATE INDEX SACO_SAMP_FK_I ON SAMPLE_COMPONENTS (SAMP_ID);
CREATE INDEX SAIN_PROC_FK_I ON SAMPLE_INPUTS (PROC_ID);
CREATE INDEX SAIN_SAMP_FK_I ON SAMPLE_INPUTS (SAMP_ID);
CREATE INDEX SAMP_INVA_FK_I ON SAMPLES (INVA_ID);
CREATE INDEX SAMP_PERS_FK_I ON SAMPLES (PERS_ID_REGISTERER);
CREATE INDEX SAMP_SAMP_FK_I_CONTROL_LAYOUT ON SAMPLES (SAMP_ID_CONTROL_LAYOUT);
CREATE INDEX SAMP_SAMP_FK_I_GENERATED_FROM ON SAMPLES (SAMP_ID_GENERATED_FROM);
CREATE INDEX SAMP_SAMP_FK_I_TOP ON SAMPLES (SAMP_ID_TOP);
CREATE INDEX SAMP_SATY_FK_I ON SAMPLES (SATY_ID);
CREATE INDEX SCMA_MABA_FK_I ON SAMPLE_COMPONENT_MATERIALS (MABA_ID);
CREATE INDEX SCMA_SACO_FK_I ON SAMPLE_COMPONENT_MATERIALS (SACO_ID);
