Sep 30, 2010

ADD FOREIGN KEY TO VERSIONED TABLE

   ORACLE WORKSPACE MANAGER  TOPIC

    If there is  a version enabled table 'BILL' and
  
 /*  CREATE TABLE BILL (N_BILLID NUMBER(10), CONSTRAINT PK_BILL PRIMARY KEY (N_BILLID));
   EXEC DBMS_WM.ENABLEVERSIONING('BILL') ; */
   
 now i created a new table BILL_DETAILS which must be a child table of BILL
 
 /* CREATE TABLE BILL_DETAILS ( N_BILLDTID NUMBER(10),CONSTRAINT PK_BILLDETAILS PRIMARY KEY (N_BILLDTID) ,   N_BILLID NUMBER(10) , S_DESC VARCHAR2 (100), D_SYSDATE  DATE   DEFAULT SYSDATE ) ; */
  
 here you are not able to add a foreign key between one versioned and one non-versioned table.if it is done  oracle error will be the result


 ORA-20200: UNSUPPORTED CONSTRAINT
   
for that you would need to execute dbms_wm.enableversioning on the non-versioned table (you can add a foreign key between 2 version enabled tables) if you don't  want the detail to be kept as version enabled table then you can disable versioning of the detail table.

    EXEC DBMS_WM.ENABLEVERSIONING('BILL_DETAILS') 
  
    EXEC DBMS_WM.BEGINDDL('BILL');
  
    EXEC DBMS_WM.BEGINDDL('BILL_DETAILS');
  
   ALTER TABLE    BILL_DETAILS_LTS ADD CONSTRAINT FK_BILLDET_BILL FOREIGN KEY (N_BILLID)  REFERENCES BILL_LTS(N_BILLID);
  
   EXEC DBMS_WM.COMMITDDL('BILL');
  
   EXEC DBMS_WM.COMMITDDL('BILL_DETAILS');

   EXEC DBMS_WM.DISABLEVERSIONING('BILL_DETAILS')   ;

No comments: