Drop foreign key from a Versioned Table

To drop a foreign key constraint from a version enable table .

EXECUTE DBMS_WM.BeginDDL('PARENT_TABLE');
EXECUTE DBMS_WM.BeginDDL('CHILD_TABLE');
ALTER TABLE CHILD_TABLE_LTS DROP CONSTRAINT FOREIGN_KEY_NAME
EXECUTE DBMS_WM.CommitDDL('CHILD_TABLE');
EXECUTE DBMS_WM.CommitDDL('PARENT_TABLE');


The foreign key constraints and its details of version enabled tables can be obtained from the view USER_WM_RIC_INFO or from
table wmsys.wm$ric_table rt

--------------------
Eg :
EXEC DBMS_WM.BEGINDDL('BILL');
EXEC DBMS_WM.BEGINDDL('BILL_DETAILS');
ALTER TABLE BILL_DETAILS_LTS DROP CONSTRAINT FK_BILL_DETAILS_CTS ;
EXEC DBMS_WM.COMMITDDL('BILL');
EXEC DBMS_WM.COMMITDDL('BILL_DETAILS');

Much More About Constraints

PRIMARY KEY LOOK UP DURING FOREIGN KEY CREATION


The lookup of matching primary keys at time of foreign key insertion takes time.In realease Oracle 9i , the first 256 primary keys can be cached so the addition of multiple foreign keys become significantly faster .The cache is only set up after the second row is processed.this avoid overhead of setting up a cache for single row DML.

----------------------
Constraints On View
--------------------
Constraint definitions are done on View from Oracle 9i onwards.
Views constraint definitions are declarative in nature;therefore DML operations on view are subject to the constraints defined on base tables.
defining constraints on base table is necessary ,not only for data correctness and cleanliness but also for MV query

NOT NULL and CHECK constraint are not supported on Views

For Creating View with constraints you must specify [RELY|NORELY ](Allows/disallows query rewrites) DISABLE NOVALIDATE (valid state for view constraint )
Otherwise it will result an error message.