-- phpMyAdmin SQL Dump
-- version 2.9.1.1
-- http://www.phpmyadmin.net
-- 
-- Host: localhost
-- Generation Time: Dec 07, 2006 at 09:20 AM
-- Server version: 5.0.26
-- PHP Version: 5.1.6-pl6-gentoo

-- --------------------------------------------------------

-- 
-- Table structure for table `ALLELE_VALIDS`
-- 

CREATE TABLE `ALLELE_VALIDS` (
  `VARIANT` int(38) NOT NULL default '0',
  `ALLELE_ID` int(10) NOT NULL default '0',
  `VALUE` varchar(30) default NULL,
  PRIMARY KEY  (`VARIANT`,`ALLELE_ID`),
  KEY `XIE1ALLELE_VALIDS` (`ALLELE_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

-- 
-- Table structure for table `ANSWER`
-- 

CREATE TABLE `ANSWER` (
  `ANSWER_ID` int(5) NOT NULL auto_increment,
  `ANSWER_VALUE` varchar(100) default NULL,
  PRIMARY KEY  (`ANSWER_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `ANSWER_GROUP`
-- 

CREATE TABLE `ANSWER_GROUP` (
  `ANSWER_GROUP_ID` int(10) NOT NULL auto_increment,
  `ANSWER_GROUP_NAME` varchar(100) default NULL,
  PRIMARY KEY  (`ANSWER_GROUP_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `ANSWER_GROUP_DETAIL`
-- 

CREATE TABLE `ANSWER_GROUP_DETAIL` (
  `ANSWER_GROUP_ID` int(10) NOT NULL default '0',
  `ANSWER_ID` int(5) NOT NULL default '0',
  PRIMARY KEY  (`ANSWER_GROUP_ID`,`ANSWER_ID`),
  KEY `XIE1ANSWER_GROUP_DETAIL` (`ANSWER_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

-- 
-- Table structure for table `AUDIT_LOG`
-- 

CREATE TABLE `AUDIT_LOG` (
  `ID` int(38) NOT NULL default '0',
  `LOG_TIMESTAMP` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `USER_NAME` varchar(30) default NULL,
  `OBJECT_NAME` varchar(30) default NULL,
  `ACTION` varchar(20) default NULL,
  `DATA` text,
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

-- 
-- Table structure for table `COLLECT_SITE`
-- 

CREATE TABLE `COLLECT_SITE` (
  `COLLECT_SITE_ID` int(3) NOT NULL default '0',
  `FACILITY` varchar(100) default NULL,
  `CITY` varchar(30) default NULL,
  `STATE` varchar(30) default NULL,
  `COUNTRY` varchar(50) default NULL,
  PRIMARY KEY  (`COLLECT_SITE_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

-- 
-- Table structure for table `DBSNP_LOOKUP`
-- 

CREATE TABLE `DBSNP_LOOKUP` (
  `DBSNP_ID` varchar(20) NOT NULL default '',
  `VARIANT_ID` int(38) NOT NULL default '0',
  `ORGANISM` varchar(20) default NULL,
  `MOLECULAR_TYPE` varchar(20) default NULL,
  `CREATED_IN_BUILD` int(11) default NULL,
  `UPDATED_IN_BUILD` int(11) default NULL,
  `VARIATION_CLASS` varchar(20) default NULL,
  `SEQUENCE` varchar(20) default NULL,
  PRIMARY KEY  (`DBSNP_ID`,`VARIANT_ID`),
  KEY `XIE1DBSNP_LOOKUP` (`VARIANT_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

-- 
-- Table structure for table `FORM`
-- 

CREATE TABLE `FORM` (
  `FORM_ID` int(5) NOT NULL auto_increment,
  `FORM_NAME` varchar(50) default NULL,
  `FORM_VERSION` varchar(20) default NULL,
  `FORM_DESC` blob,
  PRIMARY KEY  (`FORM_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `FORM_EVENT`
-- 

CREATE TABLE `FORM_EVENT` (
  `FORM_EVENT_ID` int(11) NOT NULL default '0',
  `EVENT_DATE` date default NULL,
  `FORM_ID` int(5) default NULL,
  `SUBJECT_ID` int(38) default NULL,
  PRIMARY KEY  (`FORM_EVENT_ID`),
  KEY `XIE1FORM_EVENT` (`SUBJECT_ID`),
  KEY `XIE2FORM_EVENT` (`FORM_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

-- 
-- Table structure for table `FORM_QUESTION`
-- 

CREATE TABLE `FORM_QUESTION` (
  `FORM_ID` int(5) NOT NULL default '0',
  `QUESTION_ID` int(10) NOT NULL default '0',
  `DISPLAY_ORDER` int(10) default NULL,
  PRIMARY KEY  (`FORM_ID`,`QUESTION_ID`),
  KEY `XIE1FORM_QUESTION` (`FORM_ID`),
  KEY `XIE2FORM_QUESTION` (`QUESTION_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

-- 
-- Table structure for table `FORM_SIGNOFF`
-- 

CREATE TABLE `FORM_SIGNOFF` (
  `FORM_ID` int(5) NOT NULL default '0',
  `SUBJECT_ID` int(38) NOT NULL default '0',
  `USER_ID1` int(11) default NULL,
  `ENTRY1_DATE` date default NULL,
  `USER_ID2` int(11) default NULL,
  `ENTRY2_DATE` date default NULL,
  `VALIDATOR_ID` int(11) default NULL,
  `VALIDATE_DATE` date default NULL,
  `NOCHART_ID` int(11) default NULL,
  `NOCHART_DATE` date default NULL,
  PRIMARY KEY  (`FORM_ID`,`SUBJECT_ID`),
  KEY `XIE1FORM_SIGNOFF` (`SUBJECT_ID`),
  KEY `XIE2FORM_SIGNOFF` (`SUBJECT_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

-- 
-- Table structure for table `GENE`
-- 

CREATE TABLE `GENE` (
  `GENE_ID` int(38) NOT NULL default '0',
  `GENE_NAME` varchar(100) default NULL,
  `COMMON_NAME` blob,
  `CHROMOSOME_NO` varchar(10) default NULL,
  `CHROMOSOME_START` int(10) default NULL,
  `CHROMOSOME_STOP` int(10) default NULL,
  `OMIM_INTEGER` varchar(25) default NULL,
  `SOURCE_DB_ID` int(8) NOT NULL default '0',
  `NOTES` blob,
  PRIMARY KEY  (`GENE_ID`),
  KEY `XIE1GENE` (`SOURCE_DB_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

-- 
-- Table structure for table `GENE_VARIANT`
-- 

CREATE TABLE `GENE_VARIANT` (
  `GENE_ID` int(38) NOT NULL default '0',
  `VARIANT_ID` int(38) NOT NULL default '0',
  PRIMARY KEY  (`GENE_ID`,`VARIANT_ID`),
  KEY `GENE_VARIANT_ibfk_2` (`VARIANT_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

-- 
-- Table structure for table `GENOTYPE_RESULT`
-- 

CREATE TABLE `GENOTYPE_RESULT` (
  `GENOTYPE_RESULT_ID` int(38) NOT NULL auto_increment,
  `SUBJECT_ID` int(38) NOT NULL default '0',
  `VARIANT_ID` int(38) default NULL,
  `METHOD_ID` int(5) NOT NULL default '0',
  `RESEARCHER_ID` int(5) default NULL,
  `EXPERIMENT_DATE` date NOT NULL default '0000-00-00',
  `USE_FLAG` char(3) default NULL,
  `CREATE_DATE` date default NULL,
  `CREATE_USER_ID` int(5) default NULL,
  `UPDATE_DATE` date default NULL,
  `UPDATE_USER_ID` int(5) default NULL,
  `ALLELE1` varchar(30) default NULL,
  `ALLELE2` varchar(30) default NULL,
  PRIMARY KEY  (`GENOTYPE_RESULT_ID`,`SUBJECT_ID`),
  KEY `XIE1GENTOYPE_RESULT` (`RESEARCHER_ID`),
  KEY `XIE2GENTOYPE_RESULT` (`CREATE_USER_ID`),
  KEY `XIE3GENTOYPE_RESULT` (`UPDATE_USER_ID`),
  KEY `XIE4GENTOYPE_RESULT` (`SUBJECT_ID`),
  KEY `XIE5GENTOYPE_RESULT` (`METHOD_ID`),
  KEY `XIE6GENTOYPE_RESULT` (`VARIANT_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `GROUP`
-- 

CREATE TABLE `GROUP` (
  `GROUP_ID` int(3) NOT NULL default '0',
  `GROUP_NAME` varchar(50) default NULL,
  PRIMARY KEY  (`GROUP_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

-- 
-- Table structure for table `MASTER`
-- 

CREATE TABLE `MASTER` (
  `SUBJECT_ID` int(38) NOT NULL auto_increment,
  `COLLECT_SITE_ID` int(3) default NULL,
  `COLLECT_DATE` date default NULL,
  `SEX` varchar(6) default NULL,
  `FATHER_ID` int(8) default NULL,
  `MOTHER_ID` int(8) default NULL,
  `RU_ID` int(11) NOT NULL default '0',
  `RANDOM_RU_ID` int(10) default NULL,
  `IU_ID` varchar(20) default NULL,
  `NIH_NIDA_ID` varchar(20) default NULL,
  `TISCHFIELD_ID` varchar(20) default NULL,
  `HOSPITAL_ID` varchar(220) default NULL,
  `PHARMGKB_ID` varchar(20) default NULL,
  `ETHNICITY` varchar(20) default NULL,
  `COHORT` int(5) default NULL,
  `TWIN_FLAG` varchar(20) default NULL,
  `DEATH_FLAG` varchar(20) default NULL,
  `NIDA_SHARE` char(1) default NULL,
  `MARITAL_STATUS` char(1) default NULL,
  `ADMITTING_MD` varchar(20) default NULL,
  `BOND1998_CATEGORY` char(1) default NULL,
  `ENTRY1_DONE` char(1) default NULL,
  `ENTRY1_DONE_DATE` date default NULL,
  `ENTRY1_DONE_USER` int(5) default NULL,
  `ENTRY2_DONE` char(1) default NULL,
  `ENTRY2_DONE_DATE` date default NULL,
  `ENTRY2_DONE_USER` int(5) default NULL,
  PRIMARY KEY  (`SUBJECT_ID`),
  KEY `XIE1MASTER` (`ENTRY1_DONE_USER`),
  KEY `XIE2MASTER` (`ENTRY2_DONE_USER`),
  KEY `XIE3MASTER` (`COLLECT_SITE_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `METHOD`
-- 

CREATE TABLE `METHOD` (
  `METHOD_ID` int(5) NOT NULL default '0',
  `METHOD_NAME` varchar(100) default NULL,
  `METHOD_DESC` blob,
  PRIMARY KEY  (`METHOD_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

-- 
-- Table structure for table `PERMISSION`
-- 

CREATE TABLE `PERMISSION` (
  `PERMISSION_ID` int(3) NOT NULL default '0',
  `PERMISSION_NAME` varchar(50) default NULL,
  PRIMARY KEY  (`PERMISSION_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

-- 
-- Table structure for table `QUESTION`
-- 

CREATE TABLE `QUESTION` (
  `QUESTION_ID` int(10) NOT NULL auto_increment,
  `ANSWER_GROUP_ID` int(10) default NULL,
  `QUEST_NAME` varchar(200) NOT NULL default '',
  `QUEST_TEXT` blob,
  `FREEFORM` char(1) default NULL,
  PRIMARY KEY  (`QUESTION_ID`),
  KEY `XIE1QUESTION` (`ANSWER_GROUP_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `RESPONSE`
-- 

CREATE TABLE `RESPONSE` (
  `SUBJECT_ID` int(38) NOT NULL default '0',
  `FORM_EVENT_ID` int(11) NOT NULL default '0',
  `QUESTION_ID` int(10) NOT NULL default '0',
  `ANSWER1_ID` int(5) default NULL,
  `FREEFORM_ANSWER1` varchar(20) default NULL,
  `ENTRANT1_ID` int(5) default NULL,
  `ENTRY1_DATE` date default NULL,
  `ANSWER2_ID` int(5) default NULL,
  `FREEFORM_ANSWER2` varchar(20) default NULL,
  `ENTRANT2_ID` int(5) default NULL,
  `ENTRY2_DATE` date default NULL,
  `VALIDATE_ANSWER_ID` int(5) default NULL,
  `VALIDATE_FREEFORM_ANSWER` varchar(20) default NULL,
  `VALIDATOR_ID` int(5) default NULL,
  `VALIDATE_DATE` date default NULL,
  `CREATE_DATE` date default NULL,
  `CREATE_USER_ID` int(5) default NULL,
  `UPDATE_DATE` date default NULL,
  `UPDATE_USER_ID` int(5) default NULL,
  PRIMARY KEY  (`QUESTION_ID`,`FORM_EVENT_ID`,`SUBJECT_ID`),
  KEY `XIE1RESPONSE` (`FORM_EVENT_ID`),
  KEY `XIE2RESPONSE` (`QUESTION_ID`),
  KEY `XIE3RESPONSE` (`CREATE_USER_ID`),
  KEY `XIE4RESPONSE` (`UPDATE_USER_ID`),
  KEY `XIE5RESPONSE` (`VALIDATOR_ID`),
  KEY `XIE6RESPONSE` (`ENTRANT2_ID`),
  KEY `XIE7RESPONSE` (`ENTRANT1_ID`),
  KEY `XIE8RESPONSE` (`ANSWER1_ID`),
  KEY `XIE9RESPONSE` (`ANSWER2_ID`),
  KEY `XIE10RESPONSE` (`SUBJECT_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

-- 
-- Table structure for table `RESULT`
-- 

CREATE TABLE `RESULT` (
  `TEST_ID` int(38) NOT NULL default '0',
  `ENDOCRINE` varchar(25) NOT NULL default '',
  `LEVEL` int(11) NOT NULL default '0',
  PRIMARY KEY  (`TEST_ID`),
  KEY `XIE1RESULT` (`TEST_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

-- 
-- Table structure for table `ROLE`
-- 

CREATE TABLE `ROLE` (
  `ROLE_ID` int(3) NOT NULL default '0',
  `ROLE_NAME` varchar(50) default NULL,
  PRIMARY KEY  (`ROLE_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

-- 
-- Table structure for table `ROLE_PERM`
-- 

CREATE TABLE `ROLE_PERM` (
  `ROLE_ID` int(3) NOT NULL default '0',
  `PERMISSION_ID` int(3) NOT NULL default '0',
  PRIMARY KEY  (`ROLE_ID`,`PERMISSION_ID`),
  KEY `XIE1ROLE_PERM` (`PERMISSION_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

-- 
-- Table structure for table `SOURCE_DB`
-- 

CREATE TABLE `SOURCE_DB` (
  `SOURCE_DB_ID` int(8) NOT NULL default '0',
  `DB_NAME` varchar(100) default NULL,
  `SOURCE_DESC` varchar(200) default NULL,
  `BUILD_INTEGER` varchar(25) default NULL,
  PRIMARY KEY  (`SOURCE_DB_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

-- 
-- Table structure for table `SUBJECT`
-- 

CREATE TABLE `SUBJECT` (
  `SUBJECT_ID` int(38) NOT NULL default '0',
  `FIRST_NAME` varchar(30) default NULL,
  `MIDDLE_NAME` varchar(30) default NULL,
  `LAST_NAME` varchar(30) default NULL,
  `SSN` varchar(9) default NULL,
  `BIRTH_DATE` date default NULL,
  `PATIENT_FLAG` char(3) default NULL,
  `FAMILY_PEDIGREE_NUMBER` int(8) default NULL,
  `ADDRESS` varchar(100) default NULL,
  `ADDRESS2` varchar(100) default NULL,
  `CITY` varchar(30) default NULL,
  `STATE` varchar(30) default NULL,
  `ZIPCODE` varchar(10) default NULL,
  `COUNTRY` varchar(50) default NULL,
  `PHONE` varchar(20) default NULL,
  `REFERRED_FROM` varchar(50) default NULL,
  `NOTE` varchar(200) default NULL,
  `CREATE_DATE` date default NULL,
  `CREATE_USER_ID` int(5) default NULL,
  `UPDATE_DATE` date default NULL,
  `UPDATE_USER_ID` int(5) default NULL,
  `NIDA_SUBMITTED_DATE` date default NULL,
  PRIMARY KEY  (`SUBJECT_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

-- 
-- Table structure for table `TEST`
-- 

CREATE TABLE `TEST` (
  `TEST_ID` int(38) NOT NULL default '0',
  `SUBJECT_ID` int(38) NOT NULL default '0',
  `TEST_DATE` date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`TEST_ID`),
  KEY `XIE1TEST` (`SUBJECT_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

-- 
-- Table structure for table `USER`
-- 

CREATE TABLE `USER` (
  `USER_ID` int(5) NOT NULL auto_increment,
  `USER_NAME` varchar(20) default NULL,
  `USER_PASSWORD` varchar(50) default NULL,
  `FIRST_NAME` varchar(50) default NULL,
  `MIDDLE_NAME` varchar(30) default NULL,
  `LAST_NAME` varchar(30) default NULL,
  `USER_EMAIL` varchar(50) default NULL,
  `PHONE` varchar(20) default NULL,
  `AFFILIATION` varchar(50) default NULL,
  `ADDRESS` varchar(100) default NULL,
  `ADMIN_FLAG` char(3) default NULL,
  `CREATE_DATE` date default NULL,
  `CREATE_USER_ID` int(5) default NULL,
  `UPDATE_DATE` date default NULL,
  `UPDATE_USER_ID` int(5) default NULL,
  PRIMARY KEY  (`USER_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `USER_GROUP`
-- 

CREATE TABLE `USER_GROUP` (
  `USER_ID` int(5) NOT NULL default '0',
  `GROUP_ID` int(3) NOT NULL default '0',
  `ROLE_ID` int(3) default NULL,
  PRIMARY KEY  (`USER_ID`,`GROUP_ID`),
  KEY `XIE1GROUP` (`GROUP_ID`),
  KEY `XIE2GROUP` (`ROLE_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

-- 
-- Table structure for table `VARIANT`
-- 

CREATE TABLE `VARIANT` (
  `VARIANT_ID` int(38) NOT NULL auto_increment,
  `VARIANT_NAME` varchar(50) default NULL,
  `EXON_INTRON_LOC` blob,
  `NUCLEOTIDE_LOC` varchar(20) NOT NULL,
  `UPSTREAM_SEQ` varchar(50) NOT NULL,
  `DOWNSTREAM_SEQ` varchar(50) NOT NULL,
  `NOTES` blob NOT NULL,
  `VARIANT_TYPE` varchar(18) NOT NULL,
  `DB_ID` int(5) NOT NULL,
  `DBSNP_ID` varchar(20) NOT NULL,
  PRIMARY KEY  (`VARIANT_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- 
-- Constraints for dumped tables
-- 

-- 
-- Constraints for table `ALLELE_VALIDS`
-- 
ALTER TABLE `ALLELE_VALIDS`
  ADD CONSTRAINT `ALLELE_VALIDS_ibfk_1` FOREIGN KEY (`VARIANT`) REFERENCES `VARIANT` (`VARIANT_ID`);

-- 
-- Constraints for table `ANSWER_GROUP_DETAIL`
-- 
ALTER TABLE `ANSWER_GROUP_DETAIL`
  ADD CONSTRAINT `ANSWER_GROUP_DETAIL_ibfk_1` FOREIGN KEY (`ANSWER_ID`) REFERENCES `ANSWER` (`ANSWER_ID`),
  ADD CONSTRAINT `ANSWER_GROUP_DETAIL_ibfk_2` FOREIGN KEY (`ANSWER_GROUP_ID`) REFERENCES `ANSWER_GROUP` (`ANSWER_GROUP_ID`);

-- 
-- Constraints for table `DBSNP_LOOKUP`
-- 
ALTER TABLE `DBSNP_LOOKUP`
  ADD CONSTRAINT `DBSNP_LOOKUP_ibfk_1` FOREIGN KEY (`VARIANT_ID`) REFERENCES `VARIANT` (`VARIANT_ID`);

-- 
-- Constraints for table `FORM_EVENT`
-- 
ALTER TABLE `FORM_EVENT`
  ADD CONSTRAINT `FORM_EVENT_ibfk_1` FOREIGN KEY (`SUBJECT_ID`) REFERENCES `MASTER` (`SUBJECT_ID`) ON DELETE SET NULL,
  ADD CONSTRAINT `FORM_EVENT_ibfk_2` FOREIGN KEY (`FORM_ID`) REFERENCES `FORM` (`FORM_ID`) ON DELETE SET NULL;

-- 
-- Constraints for table `FORM_QUESTION`
-- 
ALTER TABLE `FORM_QUESTION`
  ADD CONSTRAINT `FORM_QUESTION_ibfk_1` FOREIGN KEY (`QUESTION_ID`) REFERENCES `QUESTION` (`QUESTION_ID`),
  ADD CONSTRAINT `FORM_QUESTION_ibfk_2` FOREIGN KEY (`FORM_ID`) REFERENCES `FORM` (`FORM_ID`);

-- 
-- Constraints for table `FORM_SIGNOFF`
-- 
ALTER TABLE `FORM_SIGNOFF`
  ADD CONSTRAINT `FORM_SIGNOFF_ibfk_1` FOREIGN KEY (`SUBJECT_ID`) REFERENCES `MASTER` (`SUBJECT_ID`),
  ADD CONSTRAINT `FORM_SIGNOFF_ibfk_2` FOREIGN KEY (`FORM_ID`) REFERENCES `FORM` (`FORM_ID`);

-- 
-- Constraints for table `GENE`
-- 
ALTER TABLE `GENE`
  ADD CONSTRAINT `GENE_ibfk_1` FOREIGN KEY (`SOURCE_DB_ID`) REFERENCES `SOURCE_DB` (`SOURCE_DB_ID`);

-- 
-- Constraints for table `GENE_VARIANT`
-- 
ALTER TABLE `GENE_VARIANT`
  ADD CONSTRAINT `GENE_VARIANT_ibfk_1` FOREIGN KEY (`GENE_ID`) REFERENCES `GENE` (`GENE_ID`),
  ADD CONSTRAINT `GENE_VARIANT_ibfk_2` FOREIGN KEY (`VARIANT_ID`) REFERENCES `VARIANT` (`VARIANT_ID`);

-- 
-- Constraints for table `GENOTYPE_RESULT`
-- 
ALTER TABLE `GENOTYPE_RESULT`
  ADD CONSTRAINT `GENOTYPE_RESULT_ibfk_1` FOREIGN KEY (`CREATE_USER_ID`) REFERENCES `USER` (`USER_ID`) ON DELETE SET NULL,
  ADD CONSTRAINT `GENOTYPE_RESULT_ibfk_2` FOREIGN KEY (`UPDATE_USER_ID`) REFERENCES `USER` (`USER_ID`) ON DELETE SET NULL,
  ADD CONSTRAINT `GENOTYPE_RESULT_ibfk_3` FOREIGN KEY (`RESEARCHER_ID`) REFERENCES `USER` (`USER_ID`) ON DELETE SET NULL,
  ADD CONSTRAINT `GENOTYPE_RESULT_ibfk_4` FOREIGN KEY (`SUBJECT_ID`) REFERENCES `MASTER` (`SUBJECT_ID`),
  ADD CONSTRAINT `GENOTYPE_RESULT_ibfk_5` FOREIGN KEY (`METHOD_ID`) REFERENCES `METHOD` (`METHOD_ID`),
  ADD CONSTRAINT `GENOTYPE_RESULT_ibfk_6` FOREIGN KEY (`VARIANT_ID`) REFERENCES `VARIANT` (`VARIANT_ID`);

-- 
-- Constraints for table `MASTER`
-- 
ALTER TABLE `MASTER`
  ADD CONSTRAINT `MASTER_ibfk_1` FOREIGN KEY (`ENTRY1_DONE_USER`) REFERENCES `USER` (`USER_ID`),
  ADD CONSTRAINT `MASTER_ibfk_2` FOREIGN KEY (`ENTRY2_DONE_USER`) REFERENCES `USER` (`USER_ID`),
  ADD CONSTRAINT `MASTER_ibfk_3` FOREIGN KEY (`COLLECT_SITE_ID`) REFERENCES `COLLECT_SITE` (`COLLECT_SITE_ID`);

-- 
-- Constraints for table `QUESTION`
-- 
ALTER TABLE `QUESTION`
  ADD CONSTRAINT `QUESTION_ibfk_1` FOREIGN KEY (`ANSWER_GROUP_ID`) REFERENCES `ANSWER_GROUP` (`ANSWER_GROUP_ID`);

-- 
-- Constraints for table `RESULT`
-- 
ALTER TABLE `RESULT`
  ADD CONSTRAINT `RESULT_ibfk_1` FOREIGN KEY (`TEST_ID`) REFERENCES `TEST` (`TEST_ID`);

-- 
-- Constraints for table `ROLE_PERM`
-- 
ALTER TABLE `ROLE_PERM`
  ADD CONSTRAINT `ROLE_PERM_ibfk_1` FOREIGN KEY (`PERMISSION_ID`) REFERENCES `PERMISSION` (`PERMISSION_ID`),
  ADD CONSTRAINT `ROLE_PERM_ibfk_2` FOREIGN KEY (`ROLE_ID`) REFERENCES `ROLE` (`ROLE_ID`);

-- 
-- Constraints for table `SUBJECT`
-- 
ALTER TABLE `SUBJECT`
  ADD CONSTRAINT `SUBJECT_ibfk_1` FOREIGN KEY (`SUBJECT_ID`) REFERENCES `MASTER` (`SUBJECT_ID`);

-- 
-- Constraints for table `TEST`
-- 
ALTER TABLE `TEST`
  ADD CONSTRAINT `TEST_ibfk_1` FOREIGN KEY (`SUBJECT_ID`) REFERENCES `MASTER` (`SUBJECT_ID`);

-- 
-- Constraints for table `USER_GROUP`
-- 
ALTER TABLE `USER_GROUP`
  ADD CONSTRAINT `USER_GROUP_ibfk_1` FOREIGN KEY (`GROUP_ID`) REFERENCES `GROUP` (`GROUP_ID`),
  ADD CONSTRAINT `USER_GROUP_ibfk_2` FOREIGN KEY (`USER_ID`) REFERENCES `USER` (`USER_ID`),
  ADD CONSTRAINT `USER_GROUP_ibfk_3` FOREIGN KEY (`ROLE_ID`) REFERENCES `ROLE` (`ROLE_ID`);