2 Replies Latest reply: Oct 1, 2010 3:39 AM by aschilling RSS

    Unable to drop foreign key on a version-enabled table

    675665
      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-Oracle
          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
            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