Skip to Content

[ORACLE 11G] drop constraint doesn't drop index

Hello,

sometimes i run into trouble until i change an existing table which is dropped and recreated.

The simple problen is, that an "alter table <<table1>> drop constraint (columns) doesn't drop the corresponding index.

How can i cange this, to make my change scripts more mature.

best regards

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Posted on Dec 15, 2015 at 08:28 AM

    That's documented. ALTER TABLE

    If you want the indexes to be dropped, specify

    ALTER TABLE <table_name> DROP UNIQUE (<columns>) DROP INDEX

    or

    ALTER TABLE <table_name> DROP PRIMARY KEY DROP INDEX


    As other constraints are not governed by indexes, the syntax doesn't apply for those.



    Add a comment
    10|10000 characters needed characters exceeded

    • Hi,

      Usually Oracle consider constraints as Auto-Indexed (it automatically creates an index for it).

      In PowerDesigner, when you attach an an index to a key, the script generation select exclusively key or index, depending on the generation options

      This is the reason the alter/drop constraint does not include the drop index clause.

      You can easily change the statement by editing the xdb file going to ::Script\Objects\Key\Drop and add the drop index clause.

      You can also do it for PKey category.

      You may even add a testing template to check if the key has an index attached and produce the drop index clause on this condition.

      Marc

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.