-- hdb_constraint_def.sql
--
-- HDB v4.00 Jeremy S. and Stan K.
--
-- Modified:
--          Apr 27,1999  Stan K.   Add NODE_PK and DEVICE_NODE_FK keys.
--          May 14,1999  Jeremy S. Redesign of the DFLTS
--          Jun 25,1999  Stan K.   Add DFLTS_DEV_TYPE_FK foreign key.
--          Jul 26,1999  Stan K.   v4.00                       
--          Jul 30,1999  Stan K.   Add FIELD_PK key 
--	    Dec  6,1999  Jeremy S. Add USER_TEMPLATES PK
-- 	    Mar  8,2000  Stan K.   Add TEMPL_NAME to TEMPL_PK
--          Mar 27,2000  Jeremy S. Add PARAM_PK and PARAM_FK
--          Mar 31,2000  Stan K.   Removed USR_TEMPL_PK primary key
--                                 Added PARAM_TEMPL_FK foreign key
--          Apr  3,2000  Stan K.   Add TEMPL_NAME to PARAM_PK
--                                 Add constraints for TEMPLATE_IDS table
--                                 and modified foreign constraints for TEMPLATES and
--                                 PARAMETERS tables.
--	    Apr  5,2000  Jeremy S. Added MENU_PK
--          Jun  8,2000  Stan K.   Added NODE_LOCATOR_FK
--                                 Added DET_DEVS_PK, DET_DEVS_DET_FK, DET_DEVS_DEV_FK
--          Sep 15,2000  Vlad S.   Remove DEVICE_DEV_TYPE_FK from DEVICE table
--                                 Add DEVICE_TEMPL_ID_FK into DEVICE table 
--                                 (columns TEMPL_NAME,DEV_TYPE_ID)
--          Sep 27,2000 Stan K.    Added primary and foreign keys for GENERATORS:
--                                 GEN_PK and GEN_DEV_ID_FK, resp.
--          Oct 16,2000 Stan K.    Replaced TEMPL_PK by: TEMPL_RECORD_PK and 
--                                                       TEMPL_FIELD_PK
--                                 Replaced foreign keys on TEMPLATES: TEMPL_DEF_FK,
--                                                                     TEMPL_ID_FK
--                                 by:                                 TEMPL_FLD_DEF_FK,
--                                                                     TEMPL_REC_ID_FK.
--                                 Added foreign key TEMPL_FIELD_REC_FK.
--                                 Removed: RECORD_PK, FIELD_PK,
--                                 RECORD_DEV_ID_FK, FIELD_REC_FK, FIELD_DEF_FK.
--                                 Added: ALARM_PK, TEMPL_REC_ALARM_FK.
--          May 14,2001 Stan K.    Removed DFLT_NAME from DFLT_PK.
--          Mar 21,2002 Stan K.    Redesigned MENU_PK for table MENUS
--          Apr 12,2002 Stan K.    Added check constraints: DEV_CK and TEMPL_RECORD_CK
--                                 for absence of '.' and at most one presence of '/'
--                                 in epics record name.
-- 
  
PROMPT Creating Primary Key on 'LOCATORS'
ALTER TABLE LOCATORS
 ADD CONSTRAINT LOCATOR_PK PRIMARY KEY 
  (ID)
/
commit
/

PROMPT Creating Primary Key on 'NODES'
ALTER TABLE NODES
 ADD CONSTRAINT NODE_PK PRIMARY KEY 
  (ID)
/
commit
/

PROMPT Creating Primary Key on 'DEVICE_TYPES'
ALTER TABLE DEVICE_TYPES
 ADD CONSTRAINT DV_TYP_PK PRIMARY KEY 
  (ID)
/
commit
/

PROMPT Creating Primary Key on 'DETECTORS'
ALTER TABLE DETECTORS
 ADD CONSTRAINT DET_PK PRIMARY KEY 
  (DET_TYPE)
/
commit
/

PROMPT Creating Primary Key on 'DETECTOR_DEVICES'
ALTER TABLE DETECTOR_DEVICES
 ADD CONSTRAINT DET_DEVS_PK PRIMARY KEY 
  (DET_TYPE,DEV_TYPE_ID)
/
commit
/
  
--PROMPT Creating Primary Key on 'RECORDS'
--ALTER TABLE RECORDS
-- ADD CONSTRAINT RECORD_PK PRIMARY KEY 
--  (NAME)
--/
--commit
--/
    
--PROMPT Creating Primary Key on 'FIELDS'
--ALTER TABLE FIELDS
-- ADD CONSTRAINT FIELD_PK PRIMARY KEY 
--  (RECORD_NAME,RECORD_TYPE,FIELD_NAME)
--/
--commit
--/
 

PROMPT Creating Primary Key on 'TEMPLATE_IDS'
ALTER TABLE TEMPLATE_IDS
  ADD CONSTRAINT TEMPL_ID_PK PRIMARY KEY
  (DEV_TYPE,TEMPL_NAME)
/
commit
/

--PROMPT Creating Primary Key on 'TEMPLATES'
--ALTER TABLE TEMPLATES
-- ADD CONSTRAINT TEMPL_PK PRIMARY KEY
--  (TEMPL_NAME,DFLT_NAME,DEV_TYPE,FIELD_NAME,RECORD_TYPE)
--/
--commit
--/

PROMPT Creating Primary Key on TEMPL_RECORDS'
ALTER TABLE TEMPL_RECORDS
  ADD CONSTRAINT TEMPL_RECORD_PK PRIMARY KEY
   (TEMPL_NAME,DFLT_NAME)
/
commit
/

PROMPT Creating Primary Key on TEMPL_FIELDS'
ALTER TABLE TEMPL_FIELDS
  ADD CONSTRAINT TEMPL_FIELD_PK PRIMARY KEY
   (TEMPL_NAME,DFLT_NAME,FIELD_NAME,RECORD_TYPE)
/
commit
/

PROMPT Creating Primary Key on 'PARAMETERS'
ALTER TABLE PARAMETERS
 ADD CONSTRAINT PARAM_PK PRIMARY KEY
  (DEV_TYPE, TEMPL_NAME, PARAM_NAME)
/
commit
/  
    
PROMPT Creating Primary Key on 'MENUS'
ALTER TABLE MENUS
 ADD CONSTRAINT MENU_PK PRIMARY KEY
--  (FIELD_NAME, MENU_VAL)
  (MENU, CHOICE_NAME)
/
commit
/  

PROMPT Creating Primary Key on 'DEFAULTS'
ALTER TABLE DEFAULTS
 ADD CONSTRAINT DFLT_PK PRIMARY KEY
  (FIELD_NAME,RECORD_TYPE)
/
commit
/
    
PROMPT Creating Primary Key on 'EPICS_DEFINITIONS'
ALTER TABLE EPICS_DEFINITIONS
 ADD CONSTRAINT EPICS_DEF_PK PRIMARY KEY 
  (FIELD_NAME,RECORD_TYPE)
/
commit
/

PROMPT Creating Primary Key on 'DEVICES'
ALTER TABLE DEVICES
 ADD CONSTRAINT DEV_PK PRIMARY KEY 
  (ID)
/
commit
/

PROMPT Creating Primary Key on 'GENERATORS'
ALTER TABLE GENERATORS
 ADD CONSTRAINT GEN_PK PRIMARY KEY 
  (DEVICE_ID,PARAM_NAME)
/
commit
/

PROMPT Creating Primary Key on 'ALARMS'
ALTER TABLE ALARMS
 ADD CONSTRAINT ALARM_PK PRIMARY KEY
  (ID)
/
commit
/

PROMPT Creating Foreign Keys on 'DETECTOR_DEVICES'
ALTER TABLE DETECTOR_DEVICES ADD CONSTRAINT
 DET_DEVS_DET_FK FOREIGN KEY
  (DET_TYPE) REFERENCES DETECTORS
  (DET_TYPE) ADD CONSTRAINT
 DET_DEVS_DEV_FK FOREIGN KEY
  (DEV_TYPE_ID) REFERENCES DEVICE_TYPES
  (ID)
/
commit
/

PROMPT Creating Foreign Keys on 'NODES'
ALTER TABLE NODES ADD CONSTRAINT
 NODE_LOCATOR_FK FOREIGN KEY 
  (LOCATOR_ID) REFERENCES LOCATORS
  (ID)
/
commit
/

PROMPT Creating Foreign Keys on 'DEVICES'
ALTER TABLE DEVICES ADD CONSTRAINT
 DEVICE_DET_FK FOREIGN KEY 
  (DET_TYPE) REFERENCES DETECTORS
  (DET_TYPE) ADD CONSTRAINT
 DEVICE_LOCATOR_FK FOREIGN KEY 
  (LOCATOR_ID) REFERENCES LOCATORS
  (ID) ADD CONSTRAINT
 DEVICE_NODE_FK FOREIGN KEY 
  (NODE_ID) REFERENCES NODES
  (ID) ADD CONSTRAINT
 DEVICE_TEMPL_ID_FK FOREIGN KEY
  (TEMPL_NAME,DEV_TYPE_ID) REFERENCES TEMPLATE_IDS
  (TEMPL_NAME,DEV_TYPE) 
/
commit
/
  
--PROMPT Creating Foreign Keys on 'RECORDS'
--ALTER TABLE RECORDS ADD CONSTRAINT
-- RECORD_DEV_ID_FK FOREIGN KEY 
--  (DEVICE_ID) REFERENCES DEVICES
--  (ID)
--/
--commit
--/
    
--PROMPT Creating Foreign Keys on 'FIELDS'
--ALTER TABLE FIELDS ADD CONSTRAINT
-- FIELD_REC_FK FOREIGN KEY
--  (RECORD_NAME) REFERENCES RECORDS
--  (NAME) ADD CONSTRAINT
-- FIELD_DEF_FK FOREIGN KEY
--  (FIELD_NAME,RECORD_TYPE) REFERENCES EPICS_DEFINITIONS
--  (FIELD_NAME,RECORD_TYPE)
--/
--commit
--/

PROMPT Creating Foreign Keys on 'TEMPLATE_IDS'
ALTER TABLE TEMPLATE_IDS ADD CONSTRAINT
  TEMPL_ID_DEV_TYPE_FK FOREIGN KEY
    (DEV_TYPE) REFERENCES DEVICE_TYPES
    (ID)
/
commit
/

--PROMPT Creating Foreign Keys on 'TEMPLATES'
--ALTER TABLE TEMPLATES ADD CONSTRAINT
-- TEMPL_DEF_FK FOREIGN KEY 
--  (FIELD_NAME,RECORD_TYPE) REFERENCES EPICS_DEFINITIONS 
--  (FIELD_NAME,RECORD_TYPE) ADD CONSTRAINT
-- TEMPL_ID_FK FOREIGN KEY
--  (DEV_TYPE,TEMPL_NAME) REFERENCES TEMPLATE_IDS
--  (DEV_TYPE,TEMPL_NAME)
--/
--commit
--/

PROMPT Creating Foreign Keys on 'TEMPL_RECORDS'
ALTER TABLE TEMPL_RECORDS ADD CONSTRAINT
 TEMPL_REC_ID_FK FOREIGN KEY
  (DEV_TYPE,TEMPL_NAME) REFERENCES TEMPLATE_IDS
  (DEV_TYPE,TEMPL_NAME) ADD CONSTRAINT
 TEMPL_REC_ALARM_FK FOREIGN KEY 
  (ALARM_ID) REFERENCES ALARMS
  (ID)
/
commit
/

PROMPT Creating Foreign Keys on 'TEMPL_FIELDS'
ALTER TABLE TEMPL_FIELDS ADD CONSTRAINT
 TEMPL_FIELD_REC_FK FOREIGN KEY
  (TEMPL_NAME,DFLT_NAME) REFERENCES TEMPL_RECORDS
  (TEMPL_NAME,DFLT_NAME) ADD CONSTRAINT
 TEMPL_FLD_DEF_FK FOREIGN KEY 
  (FIELD_NAME,RECORD_TYPE) REFERENCES EPICS_DEFINITIONS 
  (FIELD_NAME,RECORD_TYPE)
/
commit
/

PROMPT Creating Foreign Keys on 'PARAMETERS'
ALTER TABLE PARAMETERS ADD CONSTRAINT
  PARAM_TEMPL_ID_FK FOREIGN KEY 
   (DEV_TYPE,TEMPL_NAME) REFERENCES TEMPLATE_IDS 
   (DEV_TYPE,TEMPL_NAME) 
/
commit
/

PROMPT Creating Foreign Keys on 'DEFAULTS'
ALTER TABLE DEFAULTS ADD CONSTRAINT
 DFLTS_DEF_FK FOREIGN KEY 
  (FIELD_NAME,RECORD_TYPE) REFERENCES EPICS_DEFINITIONS 
  (FIELD_NAME,RECORD_TYPE)
/
commit
/

PROMPT Creating Foreign Keys on 'GENERATORS'
ALTER TABLE GENERATORS ADD CONSTRAINT
 GEN_DEV_ID_FK FOREIGN KEY 
  (DEVICE_ID) REFERENCES DEVICES
  (ID)
/
commit
/

PROMPT Creating Check Constraint on 'DEVICES'
ALTER TABLE DEVICES ADD CONSTRAINT
 DEV_CK CHECK
  ((INSTR(ID,'.',1) = 0) AND (INSTR(ID,'/',1) = 0))
/
commit
/

PROMPT Creating Check Constraint on 'TEMPL_RECORDS'
ALTER TABLE TEMPL_RECORDS ADD CONSTRAINT
 TEMPL_RECORD_CK CHECK
  ((INSTR(DFLT_NAME,'.',1) = 0) AND (INSTR(DFLT_NAME,'/',1,2) = 0))
/
commit
/
