/************************************************************************
 * Name     :update_objects.sql						*
 * Issue    :03-09-2003 - H. Korte - Intevation - Osnabrueck		*	
 * Purpose  :create constraints after import				*
 * Parameter:								*
 * Changes  :								*
 * [ Date   ] - [ Name ] - [ Action ]					*
 ************************************************************************/

/********************************************************
 * 1 - create additional constraints			*
 ********************************************************/

ALTER TABLE USER_TAB ADD  CONSTRAINT USER_1_FK
 FOREIGN KEY (GROUP_ID) 
  REFERENCES GROUP_TAB (GROUP_ID) ;

ALTER TABLE CATCH_TAB ADD  CONSTRAINT CATCH_1_FK
 FOREIGN KEY (USER_ID) 
  REFERENCES USER_TAB (USER_ID) ;

ALTER TABLE STRETCH_TAB ADD  CONSTRAINT STRETCH_1_FK
 FOREIGN KEY (CATCH_ID) 
  REFERENCES CATCH_TAB (CATCH_ID) ON DELETE CASCADE;

ALTER TABLE DISCH_TAB ADD  CONSTRAINT DISCH_1_FK
 FOREIGN KEY (CATCH_ID, STRETCH_ID) 
  REFERENCES STRETCH_TAB (CATCH_ID, STRETCH_ID) ON DELETE CASCADE;

ALTER TABLE BACKGRD_JOIN_TAB ADD  CONSTRAINT BACKGRD_2_FK
 FOREIGN KEY (BIN_OBJ_ID) 
  REFERENCES BIN_OBJ_TAB (BIN_OBJ_ID) ON DELETE CASCADE;

ALTER TABLE SUBST_TAB ADD  CONSTRAINT SUBST_1_FK
 FOREIGN KEY (USER_ID) 
 REFERENCES USER_TAB (USER_ID) ;
 
ALTER TABLE SUBST_DET_TAB ADD  CONSTRAINT SUBST_DET_1_FK
 FOREIGN KEY (SUBST_ID) 
  REFERENCES SUBST_TAB (SUBST_ID) ON DELETE CASCADE;

ALTER TABLE ENV_TAB ADD  CONSTRAINT ENV_1_FK
 FOREIGN KEY (USER_ID) 
  REFERENCES USER_TAB (USER_ID) ;

ALTER TABLE ENV_DET_TAB ADD  CONSTRAINT ENV_DET_1_FK
 FOREIGN KEY (ENV_ID) 
  REFERENCES ENV_TAB (ENV_ID) ON DELETE CASCADE;

ALTER TABLE MARKET_TAB ADD  CONSTRAINT MARKET_1_FK
 FOREIGN KEY (CATCH_ID, DISCH_ID) 
  REFERENCES DISCH_TAB (CATCH_ID, DISCH_ID) ON DELETE CASCADE;

ALTER TABLE MARKET_TAB ADD  CONSTRAINT MARKET_2_FK
 FOREIGN KEY (USER_ID) 
  REFERENCES USER_TAB (USER_ID) ;

ALTER TABLE MOD_TAB ADD  CONSTRAINT MOD_1_FK
 FOREIGN KEY (USER_ID) 
  REFERENCES USER_TAB (USER_ID);
  
ALTER TABLE MOD_DET_TAB ADD  CONSTRAINT MOD_DET_1_FK
 FOREIGN KEY (MOD_ID) 
  REFERENCES MOD_TAB (MOD_ID) ON DELETE CASCADE;

ALTER TABLE RES_DISCH_TAB ADD  CONSTRAINT RES_DISCH_1_FK
 FOREIGN KEY (RES_ID) 
  REFERENCES RES_TAB (RES_ID) ON DELETE CASCADE;

ALTER TABLE RES_STRETCH_TAB ADD CONSTRAINT RES_STRETCH_1_FK
 FOREIGN KEY (RES_ID) 
  REFERENCES RES_TAB (RES_ID) ON DELETE CASCADE;

ALTER TABLE SESS_TAB ADD  CONSTRAINT SESS_1_FK
 FOREIGN KEY (USER_ID) 
  REFERENCES USER_TAB (USER_ID) ;

ALTER TABLE SESS_TAB ADD  CONSTRAINT SESS_2_FK
 FOREIGN KEY (CATCH_ID) 
  REFERENCES CATCH_TAB (CATCH_ID) ;

ALTER TABLE SESS_TAB ADD  CONSTRAINT SESS_3_FK
 FOREIGN KEY (SUBST_ID) 
  REFERENCES SUBST_TAB (SUBST_ID) ;

ALTER TABLE SESS_TAB ADD  CONSTRAINT SESS_4_FK
 FOREIGN KEY (ENV_ID) 
  REFERENCES ENV_TAB (ENV_ID) ;

ALTER TABLE SESS_TAB ADD  CONSTRAINT SESS_5_FK
 FOREIGN KEY (MOD_ID) 
  REFERENCES MOD_TAB (MOD_ID) ;

ALTER TABLE SESS_TAB ADD  CONSTRAINT SESS_6_FK
 FOREIGN KEY (RES_ID) 
  REFERENCES RES_TAB (RES_ID) ;

ALTER TABLE SESS_DISCH_TAB ADD  CONSTRAINT SESS_DISCH_1_FK
 FOREIGN KEY (CATCH_ID, DISCH_ID) 
  REFERENCES DISCH_TAB (CATCH_ID, DISCH_ID) ON DELETE CASCADE;

ALTER TABLE SESS_DISCH_TAB ADD  CONSTRAINT SESS_DISCH_2_FK
 FOREIGN KEY (USER_ID) 
  REFERENCES USER_TAB (USER_ID) ;

/* call function dbms_init_seq for every table */ 

SELECT dbms_init_seq();

/*************************************************
 *  2 - Final patching				 *
 *************************************************/

delete from subst_det_tab
where not exists (
select 'X' 
from para_tree_def_Tab 
where para_tree_def_Tab.block_id = subst_det_tab.block_id
and   para_tree_def_Tab.field_ID = subst_det_tab.field_id
and   para_tree_def_Tab.OBJ_TYPE IN ('SUBST', 'SUBST_DET'));

