This discussion is archived
2 Replies Latest reply: Oct 1, 2010 1:39 AM by aschilling RSS

Unable to drop foreign key on a version-enabled table

675665 Newbie
Currently Being Moderated
Hi,

We're using Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit and I'm trying to delete a foreign key from a version-enabled table.

The constraint shows in ALL_WM_RIC_INFO view. I run exec dbms_wm.beginddl('tablename'), when I inspect the generated <tablename>_LTS I don't see any referential integrity constraints generated on that table ? The constraint i'm trying to delete is from a version-enabled table to a non-version enabled table if that makes a difference.

From what I understand the referential integrity constraint would be generated and I would be able to run something like:

ALTER TABLE <tablename>_LTS DROP CONSTRAINT <constraintname>.

I tried running the above statement using the RIC_NAME from ALL_WM_RIC_INFO view but it fails predictably with:

ORA-02443: Cannot drop constraint - nonexistent constraint
Cause: alter table drop constraint <constraint_name>

Action: make sure you supply correct constraint name.
  • 1. Re: Unable to drop foreign key on a version-enabled table
    Ben Speckhard Pro
    Currently Being Moderated
    Hi,

    We do not currently support the addition or removal of a foreign key constraint between a versioned and non-versioned table. Both of the tables need to be versioned. The only solution is to temporarily enableversion the non-versioned table, drop the constraint, and then execute disableversioning on the table.

    Regards,
    Ben
  • 2. Re: Unable to drop foreign key on a version-enabled table
    aschilling Newbie
    Currently Being Moderated
    as I ran into this today as well I feel like answering this question, as I suppose that the thread opener did the same mistake as I did, and maybe
    some others do it as well :)
    of course you need to open a DDL session on the parent table as well in order to drop foreign key constraints, just as you do when you add them.
    so the correct order to make it work would be:
    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');
    I felt kind of stupid that it took me 1 hour to figure this out ;)

    regards,

    Andreas

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points