[Dbix-class] complex relationships II

[Benjamin Hitz]

And as requested, constraints, although understand this is not  
complete - I am just trying to isolate the regions of the schema  
germaine to my questions.

> CREATE TABLE GO_ANNOTATION
> (GO_ANNOTATION_NO NUMBER(10) NOT NULL
> ,GO_NO NUMBER(10) NOT NULL
> ,FEATURE_NO NUMBER(10) NOT NULL
> ,GO_EVIDENCE VARCHAR2(40) NOT NULL
> ,ANNOTATION_TYPE VARCHAR2(40) NOT NULL
> ,SOURCE VARCHAR2(40) NOT NULL
> ,DATE_LAST_REVIEWED DATE DEFAULT SYSDATE NOT NULL
> ,DATE_CREATED DATE DEFAULT SYSDATE NOT NULL
> ,CREATED_BY VARCHAR2(12) DEFAULT SUBSTR(USER,1,12) NOT NULL
> )
>


ALTER TABLE GO_ANNOTATION
ADD (CONSTRAINT GO_ANNOTATION_UK UNIQUE
   (GO_NO
   ,FEATURE_NO
   ,GO_EVIDENCE
   ,ANNOTATION_TYPE
   ,SOURCE)

ALTER TABLE GO_ANNOTATION ADD (CONSTRAINT
GOANN_GO_FK FOREIGN KEY
   (GO_NO) REFERENCES GO
   (GO_NO))
/

ALTER TABLE GO_ANNOTATION ADD (CONSTRAINT
GOANN_FEAT_FK FOREIGN KEY
   (FEATURE_NO) REFERENCES FEATURE
   (FEATURE_NO) ON DELETE CASCADE)
/


>
> CREATE TABLE GO_REF
> (GO_REF_NO NUMBER(10) NOT NULL
> ,REFERENCE_NO NUMBER(10) NOT NULL
> ,GO_ANNOTATION_NO NUMBER(10) NOT NULL
> ,HAS_QUALIFIER VARCHAR2(1) NOT NULL
> ,HAS_SUPPORTING_EVIDENCE VARCHAR2(1) NOT NULL
> ,DATE_CREATED DATE DEFAULT SYSDATE NOT NULL
> ,CREATED_BY VARCHAR2(12) DEFAULT SUBSTR(USER,1,12) NOT NULL
> )

ALTER TABLE GO_REF
ADD (CONSTRAINT GO_REF_UK UNIQUE
   (REFERENCE_NO
   ,GO_ANNOTATION_NO)

ALTER TABLE GO_REF ADD (CONSTRAINT
GOREF_REF_FK FOREIGN KEY
   (REFERENCE_NO) REFERENCES REFERENCE
   (REFERENCE_NO) ON DELETE CASCADE)
/

ALTER TABLE GO_REF ADD (CONSTRAINT
GOREF_GOANN_FK FOREIGN KEY
   (GO_ANNOTATION_NO) REFERENCES GO_ANNOTATION
   (GO_ANNOTATION_NO) ON DELETE CASCADE)
/

> This table has foreign keys to GO table and FEATURE table, while  
> the GO_REF table acts as a linking table between GO_ANNOTATION and  
> REFERENCE.
>
> CREATE TABLE GO
> (GO_NO NUMBER(10) NOT NULL
> ,GOID NUMERIC(10) NOT NULL
> ,GO_TERM VARCHAR2(240) NOT NULL
> ,GO_ASPECT VARCHAR2(40) NOT NULL
> ,GO_DEFINITION VARCHAR2(2000)
> ,DATE_CREATED DATE DEFAULT SYSDATE NOT NULL
> ,CREATED_BY VARCHAR2(12) DEFAULT SUBSTR(USER,1,12) NOT NULL
> )
>
ALTER TABLE GO
ADD (CONSTRAINT GO_PK PRIMARY KEY
   (GO_NO)
ALTER TABLE GO
ADD (CONSTRAINT GO_TERM_UK UNIQUE
   (GO_TERM
   ,GO_ASPECT)
USING INDEX
PCTFREE 10
STORAGE
(
    PCTINCREASE 0
    INITIAL 500K
    NEXT 500K
)
TABLESPACE INDEX1)
/

ALTER TABLE GO
ADD (CONSTRAINT GO_GOID_UK UNIQUE
   (GOID)
USING INDEX
PCTFREE 5
STORAGE
(
    PCTINCREASE 0
    INITIAL 500K
    NEXT 500K
)
TABLESPACE INDEX1)
/


>
> CREATE TABLE FEATURE
> (FEATURE_NO NUMBER(10) NOT NULL
> ,FEATURE_NAME VARCHAR2(40) NOT NULL
> ,DBXREF_ID VARCHAR2(40) NOT NULL
> ,FEATURE_TYPE VARCHAR2(40) NOT NULL
> ,SOURCE VARCHAR2(40) NOT NULL
> ,COORD_VERSION DATE
> ,STOP_COORD NUMBER(10)
> ,START_COORD NUMBER(10)
> ,STRAND VARCHAR2(1)
> ,GENE_NAME VARCHAR2(10)
> ,NAME_DESCRIPTION VARCHAR2(100)
> ,GENETIC_POSITION NUMBER(5,2)
> ,HEADLINE VARCHAR2(960)
> ,DATE_CREATED DATE DEFAULT SYSDATE NOT NULL
> ,CREATED_BY VARCHAR2(12) DEFAULT SUBSTR(USER,1,12) NOT NULL
> )


ALTER TABLE FEATURE
ADD (CONSTRAINT FEATURE_PK PRIMARY KEY
   (FEATURE_NO)

ALTER TABLE FEATURE
ADD (CONSTRAINT FEAT_DBXREF_ID_UK UNIQUE
   (DBXREF_ID)


>
>
> CREATE TABLE REFERENCE
> (REFERENCE_NO NUMBER(10) NOT NULL
> ,SOURCE VARCHAR2(40) NOT NULL
> ,STATUS VARCHAR2(40) NOT NULL
> ,PDF_STATUS VARCHAR2(40) NOT NULL
> ,DBXREF_ID VARCHAR2(40) NOT NULL
> ,CITATION VARCHAR2(480) NOT NULL
> ,YEAR NUMBER(4) NOT NULL
> ,PUBMED NUMBER(10)
> ,DATE_PUBLISHED VARCHAR2(40)
> ,DATE_REVISED NUMBER(8)
> ,ISSUE VARCHAR2(40)
> ,PAGE VARCHAR2(40)
> ,VOLUME VARCHAR2(40)
> ,TITLE VARCHAR2(400)
> ,JOURNAL_NO NUMBER(10)
> ,BOOK_NO NUMBER(10)
> ,DATE_CREATED DATE DEFAULT SYSDATE NOT NULL
> ,CREATED_BY VARCHAR2(12) DEFAULT SUBSTR(USER,1,12) NOT NULL
> )
>

ALTER TABLE REFERENCE
ADD (CONSTRAINT REFERENCE_PK PRIMARY KEY
   (REFERENCE_NO)

ALTER TABLE REFERENCE ADD (CONSTRAINT
REF_BOOK_FK FOREIGN KEY
   (BOOK_NO) REFERENCES BOOK
   (BOOK_NO))
/

ALTER TABLE REFERENCE ADD (CONSTRAINT
REF_JOUR_FK FOREIGN KEY
   (JOURNAL_NO) REFERENCES JOURNAL
   (JOURNAL_NO))

(but I think these are not relevant)


--
Ben Hitz
Senior Scientific Programmer ** Saccharomyces Genome Database ** GO  
Consortium
Stanford University ** hitz at genome.stanford.edu