-- File:	sdb_constraints.sql
-- Purpose:	Create silicon database constraints.
-- Created:	May 12, 2003 Stan K.
--                           Based on Atlas sct constraints v5_8
--                           Original PK, FK and CHECK constraints removed
--                           for tables:
--                             TEST_WAFER_E_PARAM
--                             TSTABCD
--                             TSTABCDCHS
--                             TSTDETIVS
--                             TSTDETOPTS
--                             TSTHYBRC
--                             TSTMODXY
--                             TSTMODZ
--
-- Modified:
--           May 13, 2003 Stan K
--                        Modified check constraints for TSTDETMFRS.
--                        Added check constraint for PROD_TYPE in table ITEMS.
--           May 15, 2003 Stan K
--                        Verified constraints against Atlas designer file 
--                        of 12 May 2003:
--                        - changed column order in ASSM_DESCR_PK
--                          and ASSM_ITEM_ASSM_DESCR_FK
--                        - added TSTDETIVS_PK and TSTDETIV_TEST_FK
--                        - added TSTDETCVS_PK and TSTDETCV_TEST_FK  
--                        - changed column order in ASSM_ITEMS
--                        - changed column order in SHIP_ITEM_PK
--                        - changed column order in TEST_LIST_PK
--                        - changed column order in TEST_LIMIT_PK
--                        - changed column order in DEFECT_PK
--                        Replaced '*SER_NO' by '*GLO_ID'
--           May 20, 2003 Stan K
--                        Added constraints for BATCH_LIST table.
--           May 23, 2003 Stan K
--                        Commented out check constraints for SUBSYST columns.
--           May 28, 2003 Stan K
--                        Removed constraints for TSTDETIVS and TSTDETCVS tables.
--                        Added constraints for TSTDETKEY table.
--           Jun  3, 2003 Stan K
--                        Cleanup - removed commented out statements.
--                        Changed names of check constraints.
--           Jul 18, 2003 Stan K
--                        Added constraints for TSTSENLEAK table.
--           Jul 21, 2003 Stan K
--                        Added constraints for TSTSENDIAG table.
--                        Added constraints for TSTSENMECH table.
--                        Added constraints for TSTSENIRRAD table.
--           Jul 23, 2003 Stan K
--                        Added 2 check constraints for TSTDETMFRS table.
--           Aug 29, 2003 Stan K
--                        Modified TST_DATA_PK PRIMARY KEY, for TEST_RAWDATA,
--                        from (TEST_NO) to (TEST_NO, FILENAME)
--                        - thus allowing > 1 raw data file for a test.
--
-----------------------------------------------------------------

PROMPT Creating Primary Key on 'MAIL_LISTS'
ALTER TABLE MAIL_LISTS
 ADD (CONSTRAINT MAIL_LIST_PK PRIMARY KEY 
  (NAME)
 USING INDEX 
)
/

PROMPT Creating Primary Key on 'TEST_CMNTS'
ALTER TABLE TEST_CMNTS
 ADD (CONSTRAINT TEST_CMNT_PK PRIMARY KEY 
  (CMNT_NO)
 USING INDEX 
)
/

PROMPT Creating Primary Key on 'LOCNS'
ALTER TABLE LOCNS
 ADD (CONSTRAINT LOCN_PK PRIMARY KEY 
  (LOCN_NAME)
 USING INDEX 
)
/

PROMPT Creating Primary Key on 'ASSM_DESCR'
ALTER TABLE ASSM_DESCR
 ADD (CONSTRAINT ASSM_DESCR_PK PRIMARY KEY 
  (CTYPE
  ,ASSM_CTYPE
  ,POSN)
 USING INDEX 
)
/

PROMPT Creating Primary Key on 'TSTDETMFRS'
ALTER TABLE TSTDETMFRS
 ADD (CONSTRAINT TSTDETMFR_PK PRIMARY KEY 
  (TEST_NO)
 USING INDEX 
)
/

PROMPT Creating Primary Key on 'TSTDETKEY'
ALTER TABLE TSTDETKEY
 ADD (CONSTRAINT TSTDETKEY_PK PRIMARY KEY 
  (TEST_NO)
 USING INDEX
 )
/

PROMPT Creating Primary Key on 'TSTSENLEAK'
ALTER TABLE TSTSENLEAK
 ADD (CONSTRAINT TSTSENLEAK_PK PRIMARY KEY 
  (TEST_NO)
 USING INDEX
 )
/

PROMPT Creating Primary Key on 'TSTSENDIAG'
ALTER TABLE TSTSENDIAG
 ADD (CONSTRAINT TSTSENDIAG_PK PRIMARY KEY 
  (TEST_NO)
 USING INDEX
 )
/

PROMPT Creating Primary Key on 'TSTSENMECH'
ALTER TABLE TSTSENMECH
 ADD (CONSTRAINT TSTSENMECH_PK PRIMARY KEY 
  (TEST_NO)
 USING INDEX
 )
/

PROMPT Creating Primary Key on 'TSTSENIRRAD'
ALTER TABLE TSTSENIRRAD
 ADD (CONSTRAINT TSTSENIRRAD_PK PRIMARY KEY 
  (TEST_NO)
 USING INDEX
 )
/

PROMPT Creating Primary Key on 'PERSONS'
ALTER TABLE PERSONS
 ADD (CONSTRAINT PERSON_PK PRIMARY KEY 
  (INITLS)
 USING INDEX 
)
/

PROMPT Creating Primary Key on 'ASSM_ITEMS'
ALTER TABLE ASSM_ITEMS
 ADD (CONSTRAINT ASSM_ITEM_PK PRIMARY KEY 
  (POSN
  ,CTYPE
  ,ASSM_CTYPE
  ,ASSM_GLO_ID)
 USING INDEX 
)
/

PROMPT Creating Primary Key on 'SHIP_ITEMS'
ALTER TABLE SHIP_ITEMS
 ADD (CONSTRAINT SHIP_ITEM_PK PRIMARY KEY 
  (SHIP_NO
  ,GLO_ID)
 USING INDEX 
)
/

PROMPT Creating Primary Key on 'ITEM_DESCR'
ALTER TABLE ITEM_DESCR
 ADD (CONSTRAINT ITEM_DESCR_PK PRIMARY KEY 
  (CTYPE)
 USING INDEX 
)
/

PROMPT Creating Primary Key on 'EVENT_DESCR'
ALTER TABLE EVENT_DESCR
 ADD (CONSTRAINT EVENT_DESC_PK PRIMARY KEY 
  (EVENT_NAME)
 USING INDEX 
)
/

PROMPT Creating Primary Key on 'WEB_LINKS'
ALTER TABLE WEB_LINKS
 ADD (CONSTRAINT WEB_LINK_PK PRIMARY KEY 
  (WEB_LINK_ID)
 USING INDEX 
)
/

PROMPT Creating Primary Key on 'TEST_IMAGES'
ALTER TABLE TEST_IMAGES
 ADD (CONSTRAINT TEST_IMAGE_PK PRIMARY KEY 
  (NAME
  ,TEST_NO)
 USING INDEX 
)
/

PROMPT Creating Primary Key on 'MAILS'
ALTER TABLE MAILS
 ADD (CONSTRAINT MAIL_PK PRIMARY KEY 
  (ADDRESS
  ,MAIL_LIST_NAME)
 USING INDEX 
)
/

PROMPT Creating Primary Key on 'ITEM_CMNTS'
ALTER TABLE ITEM_CMNTS
 ADD (CONSTRAINT ITEM_CMNT_PK PRIMARY KEY 
  (CMNT_NO)
 USING INDEX 
)
/

PROMPT Creating Primary Key on 'TEST_LIST'
ALTER TABLE TEST_LIST
 ADD (CONSTRAINT TEST_LIST_PK PRIMARY KEY 
  (CTYPE
  ,TEST_NAME)
 USING INDEX 
)
/

PROMPT Creating Primary Key on 'TEST_LIMITS'
ALTER TABLE TEST_LIMITS
 ADD (CONSTRAINT TEST_LIMIT_PK PRIMARY KEY 
  (PARM_NAME
  ,MFR
  ,CTYPE
  ,TEST_NAME)
 USING INDEX 
)
/

PROMPT Creating Primary Key on 'DISASSMLOGS'
ALTER TABLE DISASSMLOGS
 ADD (CONSTRAINT DISASSMLOG_PK PRIMARY KEY 
  (DISASSM_DATE
  ,GLO_ID
  ,ASSM_GLO_ID)
 USING INDEX 
)
/

PROMPT Creating Primary Key on 'DEFECTS'
ALTER TABLE DEFECTS
 ADD (CONSTRAINT DEFECT_PK PRIMARY KEY 
  (DEFECT_NAME
  ,TEST_NO
  ,CHAN_1ST)
 USING INDEX 
)
/

PROMPT Creating Primary Key on 'MFRS'
ALTER TABLE MFRS
 ADD (CONSTRAINT MFR_PK PRIMARY KEY 
  (MFR)
 USING INDEX 
)
/

PROMPT Creating Primary Key on 'ITEM_IMAGES'
ALTER TABLE ITEM_IMAGES
 ADD (CONSTRAINT IT_IMAGE_PK PRIMARY KEY 
  (GLO_ID
  ,NAME)
 USING INDEX 
)
/

PROMPT Creating Primary Key on 'TESTS'
ALTER TABLE TESTS
 ADD (CONSTRAINT TEST_PK PRIMARY KEY 
  (TEST_NO)
 USING INDEX 
)
/

PROMPT Creating Primary Key on 'SHIP'
ALTER TABLE SHIP
 ADD (CONSTRAINT SHIP_PK PRIMARY KEY 
  (SHIP_NO)
 USING INDEX 
)
/

PROMPT Creating Primary Key on 'ITEM_CATEGORIES'
ALTER TABLE ITEM_CATEGORIES
 ADD (CONSTRAINT ITEM_CAT_PK PRIMARY KEY 
  (CATEGORY)
 USING INDEX 
)
/

PROMPT Creating Primary Key on 'ITEM_EVENTS'
ALTER TABLE ITEM_EVENTS
 ADD (CONSTRAINT ITEM_EVENT_PK PRIMARY KEY 
  (ID)
 USING INDEX 
)
/

PROMPT Creating Primary Key on 'ITEMS'
ALTER TABLE ITEMS
 ADD (CONSTRAINT ITEM_PK PRIMARY KEY 
  (GLO_ID)
 USING INDEX 
)
/

PROMPT Creating Primary Key on 'MFR_ACCESS'
ALTER TABLE MFR_ACCESS
 ADD (CONSTRAINT MFR_ACCESS_PK PRIMARY KEY 
  (ID)
 USING INDEX 
)
/

PROMPT Creating Primary Key on 'STOCKS'
ALTER TABLE STOCKS
 ADD (CONSTRAINT STOCK_PK PRIMARY KEY 
  (LOCN_NAME
  ,GLO_ID)
 USING INDEX 
)
/

PROMPT Creating Primary Key on 'DEFECT_DESCR'
ALTER TABLE DEFECT_DESCR
 ADD (CONSTRAINT DEFCT_DESC_PK PRIMARY KEY 
  (DEFECT_NAME)
 USING INDEX 
)
/

PROMPT Creating Primary Key on 'TEST_DESCR'
ALTER TABLE TEST_DESCR
 ADD (CONSTRAINT TEST_DESCR_PK PRIMARY KEY 
  (TEST_NAME)
 USING INDEX 
)
/

--PROMPT Creating Primary Key on 'TEST_RAWDATA'
--ALTER TABLE TEST_RAWDATA
-- ADD (CONSTRAINT TST_DATA_PK PRIMARY KEY 
--  (TEST_NO)
-- USING INDEX 
--)
--/

PROMPT Creating Primary Key on 'TEST_RAWDATA'
ALTER TABLE TEST_RAWDATA
 ADD (CONSTRAINT TST_DATA_PK PRIMARY KEY 
  (TEST_NO, FILENAME)
 USING INDEX 
)
/

PROMPT Creating Primary Key on 'BATCH_LIST'
ALTER TABLE BATCH_LIST
 ADD (CONSTRAINT BATCH_LIST_PK PRIMARY KEY 
  (CTYPE
  ,BATCH_NAME)
 USING INDEX 
)
/

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

PROMPT Creating Unique Keys on 'LOCNS'
ALTER TABLE LOCNS 
 ADD ( CONSTRAINT LOCN_UNIQUE UNIQUE 
  (LOCN)
 USING INDEX 
)
/

-----------------------------------------------------------------
 
PROMPT Creating Check Constraints on 'ASSM_DESCR'
ALTER TABLE ASSM_DESCR
 ADD (CONSTRAINT ASSM_DESCR_POSN_000 CHECK (POSN BETWEEN 0 AND 999))
/
 
PROMPT Creating Check Constraints on 'TSTDETMFRS'
ALTER TABLE TSTDETMFRS
 ADD (CONSTRAINT TSTDETMFRS_THICK_000 CHECK (THICKNESS BETWEEN 200 AND 400))
 ADD (CONSTRAINT TSTDETMFRS_VISUAL_000 CHECK (VISUAL_INSP IN ('GOOD','BAD'))) 
 ADD (CONSTRAINT TSTDETMFRS_IS_STRUCT_000 CHECK (IS_STRUCTEST IN ('YES', 'NO')))
 ADD (CONSTRAINT TSTDETMFRS_V_DEP_000 CHECK (V_DEP BETWEEN 0 AND 400))
 ADD (CONSTRAINT TSTDETMFRS_I_LEA_000 CHECK (I_LEAK_100 BETWEEN 0 AND 99999999))
 ADD (CONSTRAINT TSTDETMFRS_I_LEA_001 CHECK (I_LEAK_300 BETWEEN 0 AND 99999999))
 ADD (CONSTRAINT TSTDETMFRS_I_LEA_002 CHECK (I_LEAK_500 BETWEEN 0 AND 99999999))
 ADD (CONSTRAINT TSTDETMFRS_I_LEA_003 CHECK (I_LEAK_700 BETWEEN 0 AND 99999999))
 ADD (CONSTRAINT TSTDETMFRS_R_BIA_000 CHECK (R_BIAS_AVER BETWEEN 0 AND 100))
 ADD (CONSTRAINT TSTDETMFRS_R_BIA_001 CHECK (R_BIAS_UPPER BETWEEN 0 AND 100))
 ADD (CONSTRAINT TSTDETMFRS_R_BIA_002 CHECK (R_BIAS_LOWER BETWEEN 0 AND 100))
 ADD (CONSTRAINT TSTDETMFRS_MISS_001 CHECK (MISS_INFO IN ('YES', 'NO')))
/

PROMPT Creating Check Constraints on 'TSTDETKEY'
ALTER TABLE TSTDETKEY
 ADD (CONSTRAINT TSTDETKEY_TEMPE_000 CHECK (TEMPERATURE BETWEEN -30 AND 100))
 ADD (CONSTRAINT TSTDETKEY_HUMID_000 CHECK (HUMIDITY BETWEEN 0 AND 100))
 ADD (CONSTRAINT TSTDETKEY_V_DEP_000 CHECK (V_DEP BETWEEN 0 AND 400))
 ADD (CONSTRAINT TSTDETKEY_I_LEA_000 CHECK (I_LEAK_100 BETWEEN 0 AND 99999999))
 ADD (CONSTRAINT TSTDETKEY_I_LEA_001 CHECK (I_LEAK_200 BETWEEN 0 AND 99999999))
 ADD (CONSTRAINT TSTDETKEY_I_LEA_002 CHECK (I_LEAK_300 BETWEEN 0 AND 99999999))
 ADD (CONSTRAINT TSTDETKEY_I_LEA_003 CHECK (I_LEAK_400 BETWEEN 0 AND 99999999))
 ADD (CONSTRAINT TSTDETKEY_I_LEA_004 CHECK (I_LEAK_500 BETWEEN 0 AND 99999999))
 ADD (CONSTRAINT TSTDETKEY_I_LEA_005 CHECK (I_LEAK_600 BETWEEN 0 AND 99999999))
 ADD (CONSTRAINT TSTDETKEY_I_LEA_006 CHECK (I_LEAK_700 BETWEEN 0 AND 99999999))
 ADD (CONSTRAINT TSTDETKEY_V_BRE_000 CHECK (V_BREAK BETWEEN 0 AND 9999))
 ADD (CONSTRAINT TSTDETKEY_MARK_000 CHECK (MARK IN (1, 2, 3, 4)))
/ 

PROMPT Creating Check Constraints on 'TSTSENMECH'
ALTER TABLE TSTSENMECH
 ADD (CONSTRAINT TSTSENMECH_VISUAL_000 CHECK (VISUAL_INSP IN ('GOOD', 'BAD')))
/ 
 
PROMPT Creating Check Constraints on 'SHIP_ITEMS'
ALTER TABLE SHIP_ITEMS
 ADD (CONSTRAINT SHIP_ITEMS_RECVD_000 CHECK (RECVD IN ('YES', 'NO')))
/
 
PROMPT Creating Check Constraints on 'ITEM_DESCR'
ALTER TABLE ITEM_DESCR
 ADD (CONSTRAINT ITEM_DESCR_NO_CO_000 CHECK (NO_COMPS BETWEEN 0 AND 999))
 ADD (CONSTRAINT ITEM_DESCR_IS_UN_000 CHECK (IS_UNIQUE IN ('YES', 'NO')))
 ADD (CONSTRAINT ITEM_DESCR_BATCH_000 CHECK (BATCH_REQUIRED IN ('YES', 'NO')))
/

PROMPT Creating Check Constraints on 'TEST_LIST'
ALTER TABLE TEST_LIST
 ADD (CONSTRAINT TEST_LIST_REQUI_000 CHECK (REQUIRED IN ('YES', 'NO')))
/
 
PROMPT Creating Check Constraints on 'DEFECTS'
ALTER TABLE DEFECTS
 ADD (CONSTRAINT DEFECTS_CHAN__000 CHECK (CHAN_1ST BETWEEN 0 AND 1536))
 ADD (CONSTRAINT DEFECTS_CHAN__001 CHECK (CHAN_LAST BETWEEN 0 AND 1536))
/
 
PROMPT Creating Check Constraints on 'MFRS'
ALTER TABLE MFRS
 ADD (CONSTRAINT MFRS_MFR_N_000 CHECK (MFR_NO BETWEEN 10 AND 99))
/
  
PROMPT Creating Check Constraints on 'TESTS'
ALTER TABLE TESTS
 ADD (CONSTRAINT TESTS_PROBL_000 CHECK (PROBLEM IN ('YES', 'NO')))
 ADD (CONSTRAINT TESTS_PASS_000 CHECK (PASS IN ('YES', 'NO')))
/
 
PROMPT Creating Check Constraints on 'SHIP'
ALTER TABLE SHIP
 ADD (CONSTRAINT SHIP_GROSS_000 CHECK (GROSS_WT BETWEEN 0 AND 9999999999))
 ADD (CONSTRAINT SHIP_NO_PA_000 CHECK (NO_PACKS BETWEEN 0 AND 999))
/
 
PROMPT Creating Check Constraints on 'ITEM_EVENTS'
ALTER TABLE ITEM_EVENTS
 ADD (CONSTRAINT ITEM_EVENTS_HI_TE_000 CHECK (HI_TEMP BETWEEN -40 AND 200))
 ADD (CONSTRAINT ITEM_EVENTS_LOW_T_000 CHECK (LOW_TEMP BETWEEN -40 AND 200))
 ADD (CONSTRAINT ITEM_EVENTS_TEMP_000 CHECK (TEMP BETWEEN -40 AND 200))
/
 
PROMPT Creating Check Constraints on 'ITEMS'
ALTER TABLE ITEMS
 ADD (CONSTRAINT ITEMS_TRASH_000 CHECK (TRASHED IN ('YES', 'NO')))
 ADD (CONSTRAINT ITEMS_ASSEM_000 CHECK (ASSEMBLED IN ('YES', 'NO')))
 ADD (CONSTRAINT ITEMS_PASSE_000 CHECK (PASSED IN ('YES', 'NO')))
 ADD (CONSTRAINT ITEMS_PROD_000 CHECK (PROD_TYPE IN ('PROTOTYPE','PRE-PRODUCTION','PRODUCTION')))
/
  
PROMPT Creating Check Constraints on 'STOCKS'
ALTER TABLE STOCKS
 ADD (CONSTRAINT STOCKS_QTY_000 CHECK (QTY BETWEEN 0 AND 9999999999))
/
 
PROMPT Creating Check Constraints on 'TEST_DESCR'
ALTER TABLE TEST_DESCR
 ADD (CONSTRAINT TEST_DESCR_GENER_000 CHECK (GENERIC IN ('YES', 'NO')))
 ADD (CONSTRAINT TEST_DESCR_NO_CH_000 CHECK (NO_CHANS BETWEEN 0 AND 1536))
/

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

PROMPT Creating Foreign Keys on 'TEST_CMNTS'
ALTER TABLE TEST_CMNTS ADD CONSTRAINT
 TEST_CMNT_TEST_FK FOREIGN KEY 
  (TEST_NO) REFERENCES TESTS
  (TEST_NO)
/

PROMPT Creating Foreign Keys on 'ASSM_DESCR'
ALTER TABLE ASSM_DESCR ADD CONSTRAINT
 ASSM_DESCR_ITEM_DESCR_FK FOREIGN KEY 
  (ASSM_CTYPE) REFERENCES ITEM_DESCR
  (CTYPE) ADD CONSTRAINT
 ASSM_DESCR_ITEM_DESCR_HAS_FK FOREIGN KEY 
  (CTYPE) REFERENCES ITEM_DESCR
  (CTYPE)
/

PROMPT Creating Foreign Keys on 'TSTDETMFRS'
ALTER TABLE TSTDETMFRS ADD CONSTRAINT
 TSTDETMFR_TEST_FK FOREIGN KEY 
  (TEST_NO) REFERENCES TESTS
  (TEST_NO)
/

PROMPT Creating Foreign Key on 'TSTDETKEY'
ALTER TABLE TSTDETKEY ADD (CONSTRAINT
 TSTDETKEY_TEST_FK FOREIGN KEY 
  (TEST_NO) REFERENCES TESTS
  (TEST_NO))
/

PROMPT Creating Foreign Key on 'TSTSENLEAK'
ALTER TABLE TSTSENLEAK ADD (CONSTRAINT
 TSTSENLEAK_TEST_FK FOREIGN KEY 
  (TEST_NO) REFERENCES TESTS
  (TEST_NO))
/

PROMPT Creating Foreign Key on 'TSTSENDIAG'
ALTER TABLE TSTSENDIAG ADD (CONSTRAINT
 TSTSENDIAG_TEST_FK FOREIGN KEY 
  (TEST_NO) REFERENCES TESTS
  (TEST_NO))
/

PROMPT Creating Foreign Key on 'TSTSENMECH'
ALTER TABLE TSTSENMECH ADD (CONSTRAINT
 TSTSENMECH_TEST_FK FOREIGN KEY 
  (TEST_NO) REFERENCES TESTS
  (TEST_NO))
/

PROMPT Creating Foreign Key on 'TSTSENIRRAD'
ALTER TABLE TSTSENIRRAD ADD (CONSTRAINT
 TSTSENIRRAD_TEST_FK FOREIGN KEY 
  (TEST_NO) REFERENCES TESTS
  (TEST_NO))
/

PROMPT Creating Foreign Keys on 'PERSONS'
ALTER TABLE PERSONS ADD CONSTRAINT
 PERSON_LOCN_FK FOREIGN KEY 
  (LOCN_NAME) REFERENCES LOCNS
  (LOCN_NAME)
/

PROMPT Creating Foreign Keys on 'ASSM_ITEMS'
ALTER TABLE ASSM_ITEMS ADD CONSTRAINT
 ASSM_ITEM_ASSM_DESCR_FK FOREIGN KEY 
  (CTYPE
  ,POSN
  ,ASSM_CTYPE) REFERENCES ASSM_DESCR
  (CTYPE
  ,POSN
  ,ASSM_CTYPE) ADD CONSTRAINT
 ASSM_ITEM_ITEM_ASSEMBLY_FK FOREIGN KEY 
  (ASSM_GLO_ID) REFERENCES ITEMS
  (GLO_ID) ADD CONSTRAINT
 ASSM_ITEM_ITEM_FK FOREIGN KEY 
  (GLO_ID) REFERENCES ITEMS
  (GLO_ID)
/

PROMPT Creating Foreign Keys on 'SHIP_ITEMS'
ALTER TABLE SHIP_ITEMS ADD CONSTRAINT
 SHIP_ITEM_SHIP_FK FOREIGN KEY 
  (SHIP_NO) REFERENCES SHIP
  (SHIP_NO) ADD CONSTRAINT
 SHIP_ITEM_ITEM_FK FOREIGN KEY 
  (GLO_ID) REFERENCES ITEMS
  (GLO_ID)
/

PROMPT Creating Foreign Keys on 'ITEM_DESCR'
ALTER TABLE ITEM_DESCR ADD CONSTRAINT
 ITEM_DESCR_ITEM_CAT_FK FOREIGN KEY 
  (CATEGORY) REFERENCES ITEM_CATEGORIES
  (CATEGORY)
/

PROMPT Creating Foreign Keys on 'WEB_LINKS'
ALTER TABLE WEB_LINKS ADD CONSTRAINT
 WEB_LINK_TEST_FK FOREIGN KEY 
  (TEST_NO) REFERENCES TESTS
  (TEST_NO)
/

PROMPT Creating Foreign Keys on 'TEST_IMAGES'
ALTER TABLE TEST_IMAGES ADD CONSTRAINT
 TEST_IMAGE_TEST_FK FOREIGN KEY 
  (TEST_NO) REFERENCES TESTS
  (TEST_NO)
/

PROMPT Creating Foreign Keys on 'MAILS'
ALTER TABLE MAILS ADD CONSTRAINT
 MAIL_MAIL_LIST_FK FOREIGN KEY 
  (MAIL_LIST_NAME) REFERENCES MAIL_LISTS
  (NAME)
/

PROMPT Creating Foreign Keys on 'ITEM_CMNTS'
ALTER TABLE ITEM_CMNTS ADD CONSTRAINT
 ITEM_CMNT_ITEM_FK FOREIGN KEY 
  (GLO_ID) REFERENCES ITEMS
  (GLO_ID) ADD CONSTRAINT
 ITEM_CMNT_PERSON_FK FOREIGN KEY 
  (INITLS) REFERENCES PERSONS
  (INITLS)
/

PROMPT Creating Foreign Keys on 'TEST_LIST'
ALTER TABLE TEST_LIST ADD CONSTRAINT
 TEST_LIST_ITEM_DESCR_FK FOREIGN KEY 
  (CTYPE) REFERENCES ITEM_DESCR
  (CTYPE) ADD CONSTRAINT
 TEST_LIST_TEST_DESCR_FK FOREIGN KEY 
  (TEST_NAME) REFERENCES TEST_DESCR
  (TEST_NAME)
/

PROMPT Creating Foreign Keys on 'TEST_LIMITS'
ALTER TABLE TEST_LIMITS ADD CONSTRAINT
 TEST_LIMIT_MFR_FK FOREIGN KEY 
  (MFR) REFERENCES MFRS
  (MFR) ADD CONSTRAINT
 TEST_LIMIT_TEST_LIST_FK FOREIGN KEY 
  (TEST_NAME
  ,CTYPE) REFERENCES TEST_LIST
  (TEST_NAME
  ,CTYPE)
/

PROMPT Creating Foreign Keys on 'DISASSMLOGS'
ALTER TABLE DISASSMLOGS ADD CONSTRAINT
 DISASSMLOG_ITEM_ASSEMBLY_FK FOREIGN KEY 
  (ASSM_GLO_ID) REFERENCES ITEMS
  (GLO_ID) ADD CONSTRAINT
 DISASSMLOG_ITEM_FK FOREIGN KEY 
  (GLO_ID) REFERENCES ITEMS
  (GLO_ID)
/

PROMPT Creating Foreign Keys on 'DEFECTS'
ALTER TABLE DEFECTS ADD CONSTRAINT
 DEFECT_DEFCT_DESC_FK FOREIGN KEY 
  (DEFECT_NAME) REFERENCES DEFECT_DESCR
  (DEFECT_NAME) ADD CONSTRAINT
 DEFECT_TEST_FK FOREIGN KEY 
  (TEST_NO) REFERENCES TESTS
  (TEST_NO)
/

PROMPT Creating Foreign Keys on 'ITEM_IMAGES'
ALTER TABLE ITEM_IMAGES ADD CONSTRAINT
 IT_IMAGE_ITEM_FK FOREIGN KEY 
  (GLO_ID) REFERENCES ITEMS
  (GLO_ID)
/

PROMPT Creating Foreign Keys on 'TESTS'
ALTER TABLE TESTS ADD CONSTRAINT
 TEST_PERSON_FK FOREIGN KEY 
  (INITLS) REFERENCES PERSONS
  (INITLS) ADD CONSTRAINT
 TEST_TEST_DESCR_FK FOREIGN KEY 
  (TEST_NAME) REFERENCES TEST_DESCR
  (TEST_NAME) ADD CONSTRAINT
 TEST_ITEM_FK FOREIGN KEY 
  (GLO_ID) REFERENCES ITEMS
  (GLO_ID) ADD CONSTRAINT
 TEST_LOCN_FK FOREIGN KEY 
  (LOCN_NAME) REFERENCES LOCNS
  (LOCN_NAME)
/

PROMPT Creating Foreign Keys on 'SHIP'
ALTER TABLE SHIP ADD CONSTRAINT
 SHIP_LOCN_FK FOREIGN KEY 
  (LOCN_NAME) REFERENCES LOCNS
  (LOCN_NAME) ADD CONSTRAINT
 SHIP_LOCN_RECEIVED_AT_FK FOREIGN KEY 
  (DEST_LOCN_NAME) REFERENCES LOCNS
  (LOCN_NAME) ADD CONSTRAINT
 SHIP_PERSON_FK FOREIGN KEY 
  (INITLS) REFERENCES PERSONS
  (INITLS) ADD CONSTRAINT
 SHIP_PERSON_SENT_TO_FK FOREIGN KEY 
  (DEST_INITLS) REFERENCES PERSONS
  (INITLS)
/

PROMPT Creating Foreign Keys on 'ITEM_EVENTS'
ALTER TABLE ITEM_EVENTS ADD CONSTRAINT
 ITEM_EVENT_EVENT_DESC_FK FOREIGN KEY 
  (EVENT_DESC_EVENT_NAME) REFERENCES EVENT_DESCR
  (EVENT_NAME) ADD CONSTRAINT
 ITEM_EVENT_ITEM_FK FOREIGN KEY 
  (ITEM_GLO_ID) REFERENCES ITEMS
  (GLO_ID) ADD CONSTRAINT
 ITEM_EVENT_LOCN_FK FOREIGN KEY 
  (LOCN_LOCN_NAME) REFERENCES LOCNS
  (LOCN_NAME)
/

PROMPT Creating Foreign Keys on 'ITEMS'
ALTER TABLE ITEMS ADD CONSTRAINT
 ITEM_ITEM_DESCR_FK FOREIGN KEY 
  (CTYPE) REFERENCES ITEM_DESCR
  (CTYPE) ADD CONSTRAINT
 ITEM_PERSON_FK FOREIGN KEY 
  (INITLS) REFERENCES PERSONS
  (INITLS) ADD CONSTRAINT
 ITEM_MFR_FK FOREIGN KEY 
  (MFR) REFERENCES MFRS
  (MFR) ADD CONSTRAINT
 ITEM_LOCN_FK FOREIGN KEY 
  (LOCN_NAME) REFERENCES LOCNS
  (LOCN_NAME)
/

PROMPT Creating Foreign Keys on 'STOCKS'
ALTER TABLE STOCKS ADD CONSTRAINT
 STOCK_ITEM_FK FOREIGN KEY 
  (GLO_ID) REFERENCES ITEMS
  (GLO_ID) ADD CONSTRAINT
 STOCK_LOCN_FK FOREIGN KEY 
  (LOCN_NAME) REFERENCES LOCNS
  (LOCN_NAME)
/

PROMPT Creating Foreign Keys on 'TEST_RAWDATA'
ALTER TABLE TEST_RAWDATA ADD CONSTRAINT
 TST_DATA_TEST_FK FOREIGN KEY 
  (TEST_NO) REFERENCES TESTS
  (TEST_NO)
/

PROMPT Creating Foreign Keys on 'BATCH_LIST'
ALTER TABLE BATCH_LIST ADD CONSTRAINT
 BATCH_LIST_ITEM_DESCR_FK FOREIGN KEY 
  (CTYPE) REFERENCES ITEM_DESCR
  (CTYPE)
/
