OpenBio*: derby-biosql.sql

--
-- conventions: 
-- <table_name>_id is primary internal id (usually autogenerated) 
-- Authors: Ewan Birney, Elia Stupka, Hilmar Lapp, Aaron Mackey 
-- 
-- Copyright Mark Schreiber. You may use, modify, and distribute this code under 
-- the terms of the Creative Commons license. 
-- 
-- comments to biosql - biosql-l@open-bio.org 
-- 
-- Migration of the MySQL schema to InnoDB by Hilmar Lapp <hlapp at gmx.net> 
-- Post-Cape Town changes by Hilmar Lapp. 
-- Singapore changes by Hilmar Lapp and Aaron Mackey. 
-- Migration of the 
-- 
-- Note that some aspects of the schema like uniqueness constraints
-- may be changed to best suit your requirements. Search for the tag
-- CONFIG and read the documentation you find there.
--
--
-- The Biosql database has bioentries. That is about it. 
CREATE TABLE biodatabase ( 
         biodatabase_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, 
         name VARCHAR ( 128 ) NOT NULL UNIQUE, 
         authority VARCHAR ( 128 ) , 
         description VARCHAR (30000) 
) ; 

CREATE INDEX db_auth on biodatabase ( authority );

-- we could insist that taxa are NCBI taxon id, but on reflection I made this 
-- an optional extra line, as many flat file formats do not have the NCBI id 
-- 
-- no organelle/sub species 
--
-- this corresponds to the node table of the NCBI taxonomy database 
-- left_value, right_value implement a nested sets model;
-- see http://www.oreillynet.com/pub/a/network/2002/11/27/bioconf.html
-- or Joe Celko's 'SQL for smarties' for more information.
CREATE TABLE taxon ( 
         taxon_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, 
         ncbi_taxon_id INTEGER NOT NULL UNIQUE, 
         parent_taxon_id INTEGER , 
         node_rank VARCHAR ( 32 ) , 
         genetic_code SMALLINT , 
         mito_genetic_code SMALLINT , 
         left_value INTEGER NOT NULL UNIQUE, 
         right_value INTEGER NOT NULL UNIQUE
 );      
CREATE INDEX taxparent ON taxon ( parent_taxon_id ); 

-- corresponds to the names table of the NCBI taxonomy databaase 
CREATE TABLE taxon_name ( 
         taxon_id INTEGER NOT NULL , 
         name VARCHAR ( 255 ) NOT NULL , 
         name_class VARCHAR ( 32 ) NOT NULL,
         UNIQUE ( name , name_class, taxon_id )
) ; 

CREATE INDEX taxnametaxonid ON taxon_name ( taxon_id ); 
CREATE INDEX taxnamename ON taxon_name ( name ); 

-- this is where the namespace (controlled vocabulary) ontology terms live 
-- we chose to have a separate table for this instead of reusing biodatabase 
CREATE TABLE ontology ( 
         ontology_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, 
         name VARCHAR ( 32 ) NOT NULL UNIQUE, 
         definition VARCHAR (30000)  
         
) ; 

-- any controlled vocab term, everything from full ontology 
-- terms eg GO IDs to the various keys allowed as qualifiers 
CREATE TABLE term ( 
         term_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY , 
         name VARCHAR ( 255 ) NOT NULL , 
         definition VARCHAR(30000) , 
         identifier VARCHAR ( 40 ) NOT NULL, 
         is_obsolete CHAR ( 1 ) NOT NULL,
         ontology_id INTEGER NOT NULL,
         UNIQUE ( name , ontology_id , is_obsolete ) , 
         UNIQUE ( identifier ) 
) ;

CREATE INDEX term_ont ON term ( ontology_id ); 

-- ontology terms have synonyms, here is how to store them.
-- Synonym is a reserved word in many RDBMSs, so the column synonym
-- may eventually be renamed to name.
CREATE TABLE term_synonym (
         synonym VARCHAR(255) NOT NULL,
         term_id INTEGER NOT NULL,
         PRIMARY KEY ( term_id , synonym ) ) ;

-- ontology terms to dbxref association: ontology terms have dbxrefs 
CREATE TABLE term_dbxref ( 
         term_id INTEGER NOT NULL , 
         dbxref_id INTEGER NOT NULL , 
         rank INTEGER , 
         PRIMARY KEY ( term_id , dbxref_id ) ) ; 

CREATE INDEX trmdbxref_dbxrefid ON term_dbxref ( dbxref_id ); 

-- relationship between controlled vocabulary / ontology term 
-- we use subject/predicate/object but this could also 
-- be thought of as child/relationship-type/parent. 
-- the subject/predicate/object naming is better as we 
-- can think of the graph as composed of statements. 
-- 
-- we also treat the relationshiptypes / predicates as 
-- controlled terms in themselves; this is quite useful 
-- as a lot of systems (eg GO) will soon require 
-- ontologies of relationship types (eg subtle differences 
-- in the partOf relationship) 
-- 
-- this table probably won't be filled for a while, the core 
-- will just treat ontologies as flat lists of terms 
CREATE TABLE term_relationship ( 
         term_relationship_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY , 
         subject_term_id INTEGER NOT NULL , 
         predicate_term_id INTEGER NOT NULL , 
         object_term_id INTEGER NOT NULL , 
         ontology_id INTEGER NOT NULL , 
         UNIQUE ( subject_term_id , predicate_term_id , object_term_id , ontology_id ) ) ; 

CREATE INDEX trmrel_predicateid ON term_relationship ( predicate_term_id ); 
CREATE INDEX trmrel_objectid ON term_relationship ( object_term_id ); 
CREATE INDEX trmrel_ontid ON term_relationship ( ontology_id ); 
-- CONFIG: you may want to add this if you can't get the optimizer to
-- use the composite index for the initial keys
--CREATE INDEX trmrel_subjectid ON term_relationship(subject_term_id); 

-- This lets one associate a single term with a term_relationship 
-- effecively allowing us to treat triples as 1st class terms.
-- 
-- At this point this table is only supported in Biojava. If you want
-- to know more about the rationale and idea behind it, read the
-- following article that Mat Pocock posted to the mailing list:
-- http://www.open-bio.org/pipermail/biosql-l/2003-October/000455.html
CREATE TABLE term_relationship_term (
        term_relationship_id INTEGER NOT NULL PRIMARY KEY ,
        term_id              INTEGER UNIQUE NOT NULL 
);

-- the infamous transitive closure table on ontology term relationships 
-- this is a warehouse approach - you will need to update this regularly 
-- 
-- the triple of (subject, predicate, object) is the same as for ontology 
-- relationships, with the exception of predicate being the greatest common 
-- denominator of the relationships types visited in the path (i.e., if 
-- relationship type A is-a relationship type B, the greatest common 
-- denominator for path containing both types A and B is B) 
-- 
-- See the GO database or Chado schema for other (and possibly better 
-- documented) implementations of the transitive closure table approach. 
CREATE TABLE term_path ( 
         term_path_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY  ,
         subject_term_id INTEGER NOT NULL , 
         predicate_term_id INTEGER NOT NULL , 
         object_term_id INTEGER NOT NULL , 
         ontology_id INTEGER NOT NULL , 
         distance INTEGER NOT NULL, 
         UNIQUE ( subject_term_id , predicate_term_id , object_term_id , ontology_id , distance ) ) ; 

CREATE INDEX trmpath_predicateid ON term_path ( predicate_term_id ); 
CREATE INDEX trmpath_objectid ON term_path ( object_term_id ); 
CREATE INDEX trmpath_ontid ON term_path ( ontology_id ); 
-- CONFIG: you may want to add this if you can't get the optimizer to
-- use the composite index for the initial keys
--CREATE INDEX trmpath_subjectid ON term_path(subject_term_id); 

-- we can be a bioentry without a biosequence, but not visa-versa 
-- most things are going to be keyed off bioentry_id 
--
-- accession is the stable id, display_id is a potentially volatile, 
-- human readable name. 
--
-- Version may be unknown, may be undefined, or may not exist for a certain
-- accession or database (namespace). We require it here to avoid RDBMS-
-- dependend enforcement variants (version is in a compound alternative key),
-- and to simplify query construction for UK look-ups. If there is no version
-- the convention is to put 0 (zero) here. Likewise, a record with a version
-- of zero means the version is to be interpreted as NULL.
--
-- not all entries have a taxon, but many do. 
--
-- one bioentry only has one taxon! (weirdo chimerias are not handled. tough) 
--
-- Name maps to display_id in bioperl. We have a different column name 
-- here to avoid confusion with the naming convention for foreign keys. 
CREATE TABLE bioentry ( 
         bioentry_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY , 
         biodatabase_id INTEGER NOT NULL , 
         taxon_id INTEGER , 
         name VARCHAR ( 40 ) NOT NULL , 
         accession VARCHAR ( 40 ) NOT NULL , 
         identifier VARCHAR ( 40 ) NOT NULL, 
         division VARCHAR ( 6 ) , 
         description VARCHAR( 4096 ) , 
         version INTEGER NOT NULL , 
         UNIQUE ( accession , biodatabase_id , version ) , 
-- CONFIG: uncomment one (and only one) of the two lines below. The
-- first puts a uniqueness constraint on the identifier column alone;
-- the other one puts a uniqueness constraint on identifier only
-- within a namespace.
--       UNIQUE ( identifier ) 
         UNIQUE ( identifier , biodatabase_id ) 
) ; 

CREATE INDEX bioentry_name ON bioentry ( name ); 
CREATE INDEX bioentry_db ON bioentry ( biodatabase_id ); 
CREATE INDEX bioentry_tax ON bioentry ( taxon_id ); 

-- 
-- bioentry-bioentry relationships: these are typed 
-- 
CREATE TABLE bioentry_relationship ( 
         bioentry_relationship_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY , 
         object_bioentry_id INTEGER NOT NULL , 
         subject_bioentry_id INTEGER NOT NULL , 
         term_id INTEGER NOT NULL , 
         rank INTEGER , 
         UNIQUE ( object_bioentry_id , subject_bioentry_id , term_id ) 
) ; 

CREATE INDEX bioentryrel_trm ON bioentry_relationship ( term_id ); 
CREATE INDEX bioentryrel_child ON bioentry_relationship (subject_bioentry_id);
-- CONFIG: you may want to add this if you can't get the optimizer to
-- use the composite index for the initial keys 
--CREATE INDEX bioentryrel_parent ON bioentry_relationship(object_bioentry_id);

-- for deep (depth > 1) bioentry relationship trees we need a transitive 
-- closure table too 
CREATE TABLE bioentry_path ( 
         object_bioentry_id INTEGER NOT NULL , 
         subject_bioentry_id INTEGER NOT NULL , 
         term_id INTEGER NOT NULL , 
         distance INTEGER NOT NULL,
         UNIQUE ( object_bioentry_id , subject_bioentry_id , term_id , distance ) ) ; 

CREATE INDEX bioentrypath_trm ON bioentry_path ( term_id ); 
CREATE INDEX bioentrypath_child ON bioentry_path ( subject_bioentry_id ); 

-- some bioentries will have a sequence 
-- biosequence because sequence is sometimes a reserved word 
CREATE TABLE biosequence ( 
         bioentry_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY , 
         version INTEGER , 
         length INTEGER , 
         alphabet VARCHAR ( 10 ) , 
         seq CLOB  
) ; 

-- database cross-references (e.g., GenBank:AC123456.1) 
--
-- Version may be unknown, may be undefined, or may not exist for a certain
-- accession or database (namespace). We require it here to avoid RDBMS-
-- dependend enforcement variants (version is in a compound alternative key),
-- and to simplify query construction for UK look-ups. If there is no version
-- the convention is to put 0 (zero) here. Likewise, a record with a version
-- of zero means the version is to be interpreted as NULL.
--
CREATE TABLE dbxref ( 
         dbxref_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY , 
         dbname VARCHAR ( 40 ) NOT NULL , 
         accession VARCHAR ( 40 ) NOT NULL , 
         version INTEGER NOT NULL , 
         UNIQUE ( accession , dbname , version ) ) ; 

CREATE INDEX dbxref_db ON dbxref ( dbname ); 

-- for roundtripping embl/genbank, we need to have the "optional ID" 
-- for the dbxref. 
-- 
-- another use of this table could be for storing 
-- descriptive text for a dbxref. for example, we may want to 
-- know stuff about the interpro accessions we store (without 
-- importing all of interpro), so we can attach the text 
-- description as a synonym 
CREATE TABLE dbxref_qualifier_value ( 
         dbxref_id INTEGER NOT NULL , 
         term_id INTEGER NOT NULL , 
         rank INTEGER NOT NULL DEFAULT 0 , 
         value VARCHAR (30000) , 
         PRIMARY KEY ( dbxref_id , term_id , rank ) ) ; 

CREATE INDEX dbxrefqual_dbx ON dbxref_qualifier_value ( dbxref_id ); 
CREATE INDEX dbxrefqual_trm ON dbxref_qualifier_value ( term_id ); 

-- Direct dblinks. It is tempting to do this 
-- from bioentry_id to bioentry_id. But that wont work 
-- during updates of one database - we will have to edit 
-- this table each time. Better to do the join through accession 
-- and db each time. Should be almost as cheap 
CREATE TABLE bioentry_dbxref ( 
         bioentry_id INTEGER NOT NULL , 
         dbxref_id INTEGER NOT NULL , 
         rank INTEGER , 
         PRIMARY KEY ( bioentry_id , dbxref_id ) ) ; 

CREATE INDEX dblink_dbx ON bioentry_dbxref ( dbxref_id ); 

-- We can have multiple references per bioentry, but one reference 
-- can also be used for the same bioentry. 
-- 
-- No two references can reference the same reference database entry 
-- (dbxref_id). This is where the MEDLINE id goes: PUBMED:123456. 
CREATE TABLE reference ( 
         reference_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY , 
         dbxref_id INTEGER NOT NULL UNIQUE, 
         location VARCHAR (30000) NOT NULL , 
         title VARCHAR (30000) , 
         authors VARCHAR (30000) , 
         crc VARCHAR ( 32 ) UNIQUE NOT NULL
) ; 

-- bioentry to reference associations 
CREATE TABLE bioentry_reference ( 
         bioentry_id INTEGER NOT NULL , 
         reference_id INTEGER NOT NULL , 
         start_pos INTEGER , 
         end_pos INTEGER , 
         rank INTEGER NOT NULL DEFAULT 0 , 
         PRIMARY KEY ( bioentry_id , reference_id , rank ) ) ; 

CREATE INDEX bioentryref_ref ON bioentry_reference ( reference_id ); 

-- We can have multiple comments per seqentry, and 
-- comments can have embedded '\n' characters 
CREATE TABLE comment ( 
         comment_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, 
         bioentry_id INTEGER NOT NULL , 
         comment_text VARCHAR (30000) NOT NULL , 
         rank INTEGER NOT NULL DEFAULT 0 ,
         UNIQUE ( bioentry_id , rank ) ) ; 

-- tag/value and ontology term annotation for bioentries goes here
CREATE TABLE bioentry_qualifier_value ( 
         bioentry_id INTEGER NOT NULL , 
         term_id INTEGER NOT NULL , 
         value VARCHAR (30000) , 
         rank INTEGER NOT NULL DEFAULT 0 , 
         UNIQUE ( bioentry_id , term_id , rank ) ) ; 

CREATE INDEX bioentryqual_trm ON bioentry_qualifier_value ( term_id ); 

-- feature table. We cleanly handle 
--   - simple locations 
--   - split locations 
--   - split locations on remote sequences 
CREATE TABLE seqfeature ( 
         seqfeature_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY , 
         bioentry_id INTEGER NOT NULL , 
         type_term_id INTEGER NOT NULL , 
         source_term_id INTEGER NOT NULL , 
         display_name VARCHAR ( 64 ) , 
         rank INTEGER NOT NULL DEFAULT 0 ,  
         UNIQUE ( bioentry_id , type_term_id , source_term_id , rank ) ) ; 

CREATE INDEX seqfeature_trm ON seqfeature ( type_term_id ); 
CREATE INDEX seqfeature_fsrc ON seqfeature ( source_term_id ); 
-- CONFIG: you may want to add this if you can't get the optimizer to
-- use the composite index for the initial keys 
--CREATE INDEX seqfeature_bioentryid ON seqfeature(bioentry_id); 

-- seqfeatures can be arranged in containment hierarchies. 
-- one can imagine storing other relationships between features, 
-- in this case the term_id can be used to type the relationship 
CREATE TABLE seqfeature_relationship ( 
         seqfeature_relationship_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY , 
         object_seqfeature_id INTEGER NOT NULL , 
         subject_seqfeature_id INTEGER NOT NULL , 
         term_id INTEGER NOT NULL , 
         rank INTEGER , 
         UNIQUE ( object_seqfeature_id , subject_seqfeature_id , term_id ) ) ; 

CREATE INDEX seqfeaturerel_trm ON seqfeature_relationship ( term_id ); 
CREATE INDEX seqfeaturerel_child ON seqfeature_relationship ( subject_seqfeature_id ); 
-- CONFIG: you may want to add this if you can't get the optimizer to
-- use the composite index for the initial keys 
--CREATE INDEX seqfeaturerel_parent ON seqfeature_relationship(object_seqfeature_id); 

-- for deep (depth > 1) seqfeature relationship trees we need a transitive 
-- closure table too 
CREATE TABLE seqfeature_path ( 
         object_seqfeature_id INTEGER NOT NULL , 
         subject_seqfeature_id INTEGER NOT NULL , 
         term_id INTEGER NOT NULL , 
         distance INTEGER NOT NULL,
         UNIQUE ( object_seqfeature_id , subject_seqfeature_id , term_id , distance ) ) ; 

CREATE INDEX seqfeaturepath_trm ON seqfeature_path ( term_id ); 
CREATE INDEX seqfeaturepath_child ON seqfeature_path ( subject_seqfeature_id );
-- CONFIG: you may want to add this if you can't get the optimizer to
-- use the composite index for the initial keys 
--CREATE INDEX seqfeaturerel_parent ON seqfeature_path(object_seqfeature_id); 

-- tag/value associations - or ontology annotations 
CREATE TABLE seqfeature_qualifier_value ( 
         seqfeature_id INTEGER NOT NULL , 
         term_id INTEGER NOT NULL , 
         rank INTEGER NOT NULL DEFAULT 0 , 
         value VARCHAR (1024)  NOT NULL , 
         PRIMARY KEY ( seqfeature_id , term_id , rank ) ) ; 

CREATE INDEX seqfeaturequal_trm ON seqfeature_qualifier_value ( term_id ); 

-- DBXrefs for features. This is necessary for genome oriented viewpoints, 
-- where you have a few have long sequences (contigs, or chromosomes) with many
-- features on them. In that case the features are the semantic scope for 
-- their annotation bundles, not the bioentry they are attached to. 
CREATE TABLE seqfeature_dbxref ( 
         seqfeature_id INTEGER NOT NULL , 
         dbxref_id INTEGER NOT NULL , 
         rank INTEGER , 
         PRIMARY KEY ( seqfeature_id , dbxref_id ) ) ; 

CREATE INDEX feadblink_dbx ON seqfeature_dbxref ( dbxref_id ); 

-- basically we model everything as potentially having 
-- any number of locations, ie, a split location. SimpleLocations 
-- just have one location. We need to have a location id for the qualifier 
-- associations of fuzzy locations. 
--
-- please do not try to model complex assemblies with this thing. It wont 
-- work. Check out the ensembl schema for this. 
--
-- we allow nulls for start/end - this is useful for fuzzies as 
-- standard range queries will not be included 
--
-- for remote locations, the join to make is to DBXref 
--
-- the FK to term is a possibility to store the type of the 
-- location for determining in one hit whether it's a fuzzy or not 
CREATE TABLE location ( 
         location_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY , 
         seqfeature_id INTEGER NOT NULL , 
         dbxref_id INTEGER , 
         term_id INTEGER , 
         start_pos INTEGER , 
         end_pos INTEGER , 
         strand INTEGER NOT NULL DEFAULT 0 , 
         rank INTEGER NOT NULL DEFAULT 0 , 
         UNIQUE ( seqfeature_id , rank ) ) ; 

CREATE INDEX seqfeatureloc_start ON location ( start_pos, end_pos ); 
CREATE INDEX seqfeatureloc_dbx ON location ( dbxref_id ); 
CREATE INDEX seqfeatureloc_trm ON location ( term_id ); 

-- location qualifiers - mainly intended for fuzzies but anything 
-- can go in here 
-- some controlled vocab terms have slots; 
-- fuzzies could be modeled as min_start(5), max_start(5) 
--  
-- there is no restriction on extending the fuzzy ontology 
-- for your own nefarious aims, although the bio* apis will 
-- most likely ignore these 
CREATE TABLE location_qualifier_value ( 
         location_id INTEGER NOT NULL , 
         term_id INTEGER NOT NULL , 
         value VARCHAR ( 255 ) NOT NULL , 
         int_value INTEGER , 
         PRIMARY KEY ( location_id , term_id ) ) ; 

CREATE INDEX locationqual_trm ON location_qualifier_value ( term_id ); 

-- 
-- Create the foreign key constraints 
-- 

-- ontology term
ALTER TABLE term ADD CONSTRAINT FKont_term
      FOREIGN KEY ( ontology_id ) REFERENCES ontology ( ontology_id ) 
      ON DELETE CASCADE ;

-- term synonyms
ALTER TABLE term_synonym ADD CONSTRAINT FKterm_syn
      FOREIGN KEY ( term_id ) REFERENCES term ( term_id )
      ON DELETE CASCADE ;

-- term_dbxref 
ALTER TABLE term_dbxref ADD CONSTRAINT FKdbxref_trmdbxref
      FOREIGN KEY ( dbxref_id ) REFERENCES dbxref ( dbxref_id )
      ON DELETE CASCADE ;
ALTER TABLE term_dbxref ADD CONSTRAINT FKterm_trmdbxref
      FOREIGN KEY ( term_id ) REFERENCES term ( term_id )
      ON DELETE CASCADE ;

-- term_relationship 
ALTER TABLE term_relationship ADD CONSTRAINT FKtrmsubject_trmrel
      FOREIGN KEY ( subject_term_id ) REFERENCES term ( term_id )
      ON DELETE CASCADE ;
ALTER TABLE term_relationship ADD CONSTRAINT FKtrmpredicate_trmrel
      FOREIGN KEY ( predicate_term_id ) REFERENCES term ( term_id )
      ON DELETE CASCADE ;
ALTER TABLE term_relationship ADD CONSTRAINT FKtrmobject_trmrel
      FOREIGN KEY ( object_term_id ) REFERENCES term ( term_id )
      ON DELETE CASCADE ;
ALTER TABLE term_relationship ADD CONSTRAINT FKontology_trmrel
      FOREIGN KEY ( ontology_id ) REFERENCES ontology ( ontology_id )
      ON DELETE CASCADE ;

-- term_relationship_term
ALTER TABLE term_relationship_term ADD CONSTRAINT FKtrmrel_trmreltrm
      FOREIGN KEY (term_relationship_id) REFERENCES term_relationship(term_relationship_id)
      ON DELETE CASCADE ;
ALTER TABLE term_relationship_term ADD CONSTRAINT FKtrm_trmreltrm
      FOREIGN KEY (term_id) REFERENCES term(term_id)
      ON DELETE CASCADE ;

-- term_path 
ALTER TABLE term_path ADD CONSTRAINT FKtrmsubject_trmpath
      FOREIGN KEY ( subject_term_id ) REFERENCES term ( term_id )
      ON DELETE CASCADE ;
ALTER TABLE term_path ADD CONSTRAINT FKtrmpredicate_trmpath
      FOREIGN KEY ( predicate_term_id ) REFERENCES term ( term_id )
      ON DELETE CASCADE ;
ALTER TABLE term_path ADD CONSTRAINT FKtrmobject_trmpath
      FOREIGN KEY ( object_term_id ) REFERENCES term ( term_id )
      ON DELETE CASCADE ;
ALTER TABLE term_path ADD CONSTRAINT FKontology_trmpath
      FOREIGN KEY ( ontology_id ) REFERENCES ontology ( ontology_id )
      ON DELETE CASCADE ;

-- taxon, taxon_name 
-- unfortunately, we can't constrain parent_taxon_id as it is violated
-- occasionally by the downloads available from NCBI
-- ALTER TABLE taxon ADD CONSTRAINT FKtaxon_taxon
--       FOREIGN KEY ( parent_taxon_id ) REFERENCES taxon ( taxon_id )
--       DEFERRABLE;
ALTER TABLE taxon_name ADD CONSTRAINT FKtaxon_taxonname
      FOREIGN KEY ( taxon_id ) REFERENCES taxon ( taxon_id )
      ON DELETE CASCADE ;

-- bioentry 
ALTER TABLE bioentry ADD CONSTRAINT FKtaxon_bioentry
      FOREIGN KEY ( taxon_id ) REFERENCES taxon ( taxon_id ) ; 
ALTER TABLE bioentry ADD CONSTRAINT FKbiodatabase_bioentry
      FOREIGN KEY ( biodatabase_id ) REFERENCES biodatabase ( biodatabase_id ) ; 
-- bioentry_relationship 
ALTER TABLE bioentry_relationship ADD CONSTRAINT FKterm_bioentryrel
      FOREIGN KEY ( term_id ) REFERENCES term ( term_id ) ; 
ALTER TABLE bioentry_relationship ADD CONSTRAINT FKparentent_bioentryrel
      FOREIGN KEY ( object_bioentry_id ) REFERENCES bioentry ( bioentry_id )
      ON DELETE CASCADE ;
ALTER TABLE bioentry_relationship ADD CONSTRAINT FKchildent_bioentryrel
      FOREIGN KEY ( subject_bioentry_id ) REFERENCES bioentry ( bioentry_id )
      ON DELETE CASCADE ;

-- bioentry_path 
ALTER TABLE bioentry_path ADD CONSTRAINT FKterm_bioentrypath
      FOREIGN KEY ( term_id ) REFERENCES term ( term_id ) ; 
ALTER TABLE bioentry_path ADD CONSTRAINT FKparentent_bioentrypath
      FOREIGN KEY ( object_bioentry_id ) REFERENCES bioentry ( bioentry_id )
      ON DELETE CASCADE ;
ALTER TABLE bioentry_path ADD CONSTRAINT FKchildent_bioentrypath
      FOREIGN KEY ( subject_bioentry_id ) REFERENCES bioentry ( bioentry_id )
      ON DELETE CASCADE ;

-- biosequence 
ALTER TABLE biosequence ADD CONSTRAINT FKbioentry_bioseq
      FOREIGN KEY ( bioentry_id ) REFERENCES bioentry ( bioentry_id )
      ON DELETE CASCADE ;

-- comment 
ALTER TABLE comment ADD CONSTRAINT FKbioentry_comment
      FOREIGN KEY ( bioentry_id ) REFERENCES bioentry ( bioentry_id )
      ON DELETE CASCADE ;

-- bioentry_dbxref 
ALTER TABLE bioentry_dbxref ADD CONSTRAINT FKbioentry_dblink
      FOREIGN KEY ( bioentry_id ) REFERENCES bioentry ( bioentry_id )
      ON DELETE CASCADE ;
ALTER TABLE bioentry_dbxref ADD CONSTRAINT FKdbxref_dblink
      FOREIGN KEY ( dbxref_id ) REFERENCES dbxref ( dbxref_id )
      ON DELETE CASCADE ;

-- dbxref_qualifier_value 
ALTER TABLE dbxref_qualifier_value ADD CONSTRAINT FKtrm_dbxrefqual
      FOREIGN KEY ( term_id ) REFERENCES term ( term_id ) ;
ALTER TABLE dbxref_qualifier_value ADD CONSTRAINT FKdbxref_dbxrefqual
      FOREIGN KEY ( dbxref_id ) REFERENCES dbxref ( dbxref_id )
      ON DELETE CASCADE ;

-- bioentry_reference 
ALTER TABLE bioentry_reference ADD CONSTRAINT FKbioentry_entryref
      FOREIGN KEY ( bioentry_id ) REFERENCES bioentry ( bioentry_id )
      ON DELETE CASCADE ;
ALTER TABLE bioentry_reference ADD CONSTRAINT FKreference_entryref
      FOREIGN KEY ( reference_id ) REFERENCES reference ( reference_id )
      ON DELETE CASCADE ;

-- reference 
ALTER TABLE reference ADD CONSTRAINT FKdbxref_reference
      FOREIGN KEY ( dbxref_id ) REFERENCES dbxref ( dbxref_id ) ;

-- bioentry_qualifier_value 
ALTER TABLE bioentry_qualifier_value ADD CONSTRAINT FKbioentry_entqual
      FOREIGN KEY ( bioentry_id ) REFERENCES bioentry ( bioentry_id )
      ON DELETE CASCADE ;
ALTER TABLE bioentry_qualifier_value ADD CONSTRAINT FKterm_entqual
      FOREIGN KEY ( term_id ) REFERENCES term ( term_id ) ;

-- seqfeature 
ALTER TABLE seqfeature ADD CONSTRAINT FKterm_seqfeature
      FOREIGN KEY ( type_term_id ) REFERENCES term ( term_id ) ; 
ALTER TABLE seqfeature ADD CONSTRAINT FKsourceterm_seqfeature
      FOREIGN KEY ( source_term_id ) REFERENCES term ( term_id ) ; 
ALTER TABLE seqfeature ADD CONSTRAINT FKbioentry_seqfeature
      FOREIGN KEY ( bioentry_id ) REFERENCES bioentry ( bioentry_id )
      ON DELETE CASCADE ;

-- seqfeature_relationship 
ALTER TABLE seqfeature_relationship ADD CONSTRAINT FKterm_seqfeatrel
      FOREIGN KEY ( term_id ) REFERENCES term ( term_id ) ;
ALTER TABLE seqfeature_relationship ADD CONSTRAINT FKparentfeat_seqfeatrel
      FOREIGN KEY ( object_seqfeature_id ) REFERENCES seqfeature ( seqfeature_id )
      ON DELETE CASCADE ;
ALTER TABLE seqfeature_relationship ADD CONSTRAINT FKchildfeat_seqfeatrel
      FOREIGN KEY ( subject_seqfeature_id ) REFERENCES seqfeature ( seqfeature_id )
      ON DELETE CASCADE ;

-- seqfeature_path 
ALTER TABLE seqfeature_path ADD CONSTRAINT FKterm_seqfeatpath
      FOREIGN KEY ( term_id ) REFERENCES term ( term_id ) ;
ALTER TABLE seqfeature_path ADD CONSTRAINT FKparentfeat_seqfeatpath
      FOREIGN KEY ( object_seqfeature_id ) REFERENCES seqfeature ( seqfeature_id )
      ON DELETE CASCADE ;
ALTER TABLE seqfeature_path ADD CONSTRAINT FKchildfeat_seqfeatpath
      FOREIGN KEY ( subject_seqfeature_id ) REFERENCES seqfeature ( seqfeature_id )
      ON DELETE CASCADE ;

-- seqfeature_qualifier_value 
ALTER TABLE seqfeature_qualifier_value ADD CONSTRAINT FKterm_featqual
      FOREIGN KEY ( term_id ) REFERENCES term ( term_id ) ;
ALTER TABLE seqfeature_qualifier_value ADD CONSTRAINT FKseqfeature_featqual
      FOREIGN KEY ( seqfeature_id ) REFERENCES seqfeature ( seqfeature_id )
      ON DELETE CASCADE ;

-- seqfeature_dbxref 
ALTER TABLE seqfeature_dbxref ADD CONSTRAINT FKseqfeature_feadblink
      FOREIGN KEY ( seqfeature_id ) REFERENCES seqfeature ( seqfeature_id )
      ON DELETE CASCADE ;
ALTER TABLE seqfeature_dbxref ADD CONSTRAINT FKdbxref_feadblink
      FOREIGN KEY ( dbxref_id ) REFERENCES dbxref ( dbxref_id )
      ON DELETE CASCADE ;

-- location 
ALTER TABLE location ADD CONSTRAINT FKseqfeature_location
      FOREIGN KEY ( seqfeature_id ) REFERENCES seqfeature ( seqfeature_id )
      ON DELETE CASCADE ;
ALTER TABLE location ADD CONSTRAINT FKdbxref_location
      FOREIGN KEY ( dbxref_id ) REFERENCES dbxref ( dbxref_id ) ;
ALTER TABLE location ADD CONSTRAINT FKterm_featloc
      FOREIGN KEY ( term_id ) REFERENCES term ( term_id ) ;

-- location_qualifier_value 
ALTER TABLE location_qualifier_value ADD CONSTRAINT FKfeatloc_locqual
      FOREIGN KEY ( location_id ) REFERENCES location ( location_id )
      ON DELETE CASCADE ;
ALTER TABLE location_qualifier_value ADD CONSTRAINT FKterm_locqual
      FOREIGN KEY ( term_id ) REFERENCES term ( term_id ) ;