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:
Post a Comment