Forum Stats

  • 3,734,267 Users
  • 2,246,933 Discussions
  • 7,857,216 Comments

Discussions

Naming conflict (ORA-00955) for index and constraint when using unique index (.. desc, .. asc)

Giovanni_66
Giovanni_66 Member Posts: 2
edited Dec 11, 2013 8:00AM in SQL & PL/SQL

Hello,

indexes and constraints are in different namespaces, therefore it should be possible to assign them the same name.

In the following case this is apparently not working:

create unique index orders_year_show_uq
  on orders (year desc, show_orders asc);

alter table orders
  add constraint orders_year_show_uq unique (year, show_order);

When I execute these statement, I get an "SQL Error: ORA-00955: name is already used by an existing object".

If I clean up and I execute the same statements as above with the only difference on line 2, using "year asc" instead of "year desc":


  on orders (year asc, show_orders asc);

then it successes.

I cannot explain it, do you have any idea?

Thanks in advance.

Kind regards

Giovanni

Tagged:

Best Answer

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,398 Black Diamond
    Accepted Answer

    First of all you have show_orders in index and show_order in constraint. I'll assume it is a typo. Secondly, when you create constraint without specifying reinforcing index Oracle looks for existing index on same set of columns in ASCending order. If such index doesn't exists (and that's your case since one of your index columns is in DESCending order), Oracle tries to create one with same name as the constraint. That's why you get ORA-00955: name is already used by an existing object. In general, you can explicitly specify index name to force Oracle using existing index. But it will not help you. If you issue

    alter table orders add constraint orders_year_show_uq unique(year, show_orders) using index orders_year_show_uq;

    You'll get ORA-14196: Specified index cannot be used to enforce the constraint.

    Anyway, Oracle doesn't support using DESC indexes for PK/UK.

    SY.

Answers

  • John Stegeman
    John Stegeman Member Posts: 24,269 Blue Diamond

    Unique constraints automatically create (or "capture," if one already exists) an index, so just create the constraint - no need for a separate index. The underlying index may be unique or not, depending upon whether the constraint is deferrable. So, you can make the error go away by not creating the index in the first place

    Having said that, it looks like you've discovered a situation where Oracle gets confused, and filing an SR with Oracle could either get that issue resolved or explained as to why it's not an issue.

    John

    John Stegeman
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,398 Black Diamond
    Accepted Answer

    First of all you have show_orders in index and show_order in constraint. I'll assume it is a typo. Secondly, when you create constraint without specifying reinforcing index Oracle looks for existing index on same set of columns in ASCending order. If such index doesn't exists (and that's your case since one of your index columns is in DESCending order), Oracle tries to create one with same name as the constraint. That's why you get ORA-00955: name is already used by an existing object. In general, you can explicitly specify index name to force Oracle using existing index. But it will not help you. If you issue

    alter table orders add constraint orders_year_show_uq unique(year, show_orders) using index orders_year_show_uq;

    You'll get ORA-14196: Specified index cannot be used to enforce the constraint.

    Anyway, Oracle doesn't support using DESC indexes for PK/UK.

    SY.

  • Ok, thank you for your answer.

This discussion has been closed.