This discussion is archived
14 Replies Latest reply: Sep 20, 2012 3:00 AM by Paul Horth RSS

Unique constraint and index relationship

839468 Newbie
Currently Being Moderated
I have created a table and added unique constraint to it.

SQL> create table test1(id nvarchar2(10));

Table created.
SQL> alter table test1 add constraint u1_test1 unique(id);

Table altered.
SQL> select index_name from user_ind_columns where table_name='TEST1';

U1_TEST1

SQL> select constraint_name from user_cons_columns where table_name='TEST1';

U1_TEST1

This shows as there was not index on column id so Oracle created background index for unique constraint.

When I drop unique constraint index also gets dropped.

SQL> alter table test1 drop constraint u1_test1;

Table altered.
SQL> select constraint_name from user_cons_columns where table_name='TEST1';

no rows selected

SQL> select index_name from user_ind_columns where table_name='TEST1';

no rows selected

Where does Oracle stores this relationship information and on drop of constraints it drops index too...?
  • 1. Re: Unique constraint and index relationship
    sybrand_b Guru
    Currently Being Moderated
    Number 1 rule in posting is:
    Always include your four-digit version as most questions have version dependent answers.
    This is one of them.
    In 11.2.0.1.0 the information is available in dba_constraints.

    Note: if the index was already there before creating the constraint, Oracle will use it to implement the constraint, but leave it alone, when you drop the constraint.
    --------
    Sybrand Bakker
    Senior Oracle DBA
  • 2. Re: Unique constraint and index relationship
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Welcome to the forum!
    836465 wrote:
    ... Where does Oracle stores this relationship information and on drop of constraints it drops index too...?
    USER_COINSTRAINTS (or ALL_CONSTRAINTS or DBA_CONSTRAINTS) has one row per index, and contains all the information that will occur (at most) once per constraint, such as INDEX_OWNER and INDEX_NAME.

    Feel free to explore the data dictionary. You won't harm anything by doing DESCRIBE and SELECT statements.
    For example, if you want to know what data dictionary views deal with constraints, and the documentation isn't helping, then you might guess that such views start with 'USER' and contain 'CONS' later in the name, so you can say:
    SELECT    view_name
    FROM      all_views
    WHERE     view_name   LIKE 'USER%CONS%'
    ORDER BY  view_name;
  • 3. Re: Unique constraint and index relationship
    Dom Brooks Guru
    Currently Being Moderated
    if the index was already there before creating the constraint, Oracle will use it to implement the constraint, but leave it alone, when you drop the constraint
    If the index is non-unique. If the index is unique then it is dropped along with the constraint
    SQL> drop table t1;
    
    Table dropped.
    
    SQL> 
    SQL> create table t1
      2  (col1 number);
    
    Table created.
    
    SQL> 
    SQL> create index i1 on t1 (col1);
    
    Index created.
    
    SQL> 
    SQL> alter table t1 add constraint u_t1 unique(col1);
    
    Table altered.
    
    SQL> 
    SQL> select index_name from user_indexes where table_name = 'T1';
    
    INDEX_NAME
    ------------------------------
    I1
    
    SQL> 
    SQL> alter table t1 drop constraint u_t1;
    
    Table altered.
    
    SQL> 
    SQL> select index_name from user_indexes where table_name = 'T1';
    
    INDEX_NAME
    ------------------------------
    I1
    
    SQL> 
    SQL> drop index i1;
    
    Index dropped.
    
    SQL> 
    SQL> create unique index i1 on t1 (col1);
    
    Index created.
    
    SQL> 
    SQL> alter table t1 add constraint u_t1 unique(col1);
    
    Table altered.
    
    SQL> 
    SQL> select index_name from user_indexes where table_name = 'T1';
    
    INDEX_NAME
    ------------------------------
    I1
    
    SQL> 
    SQL> alter table t1 drop constraint u_t1;
    
    Table altered.
    
    SQL> 
    SQL> select index_name from user_indexes where table_name = 'T1';
    
    no rows selected
    If you trace the session ( alter session set sql_trace = true) when doing your drop, then you should be able to see the recursive sql involved.

    Edited by: Dom Brooks on Feb 14, 2011 11:52 AM
    Depends on version
  • 4. Re: Unique constraint and index relationship
    760565 Explorer
    Currently Being Moderated
    I think that the original question was not about the index properties itself, but about how does Oracle know that it should drop the index upon dropping a constraint. How can we determine that the index was created implicitly or explicitly.
  • 5. Re: Unique constraint and index relationship
    839468 Newbie
    Currently Being Moderated
    Thanks,

    Yes, I will keep version information for next posts. This question was for 11.2.0.1.0.
  • 6. Re: Unique constraint and index relationship
    839468 Newbie
    Currently Being Moderated
    If you are not the owner of index then oracle does not drops it.

    SQL> create table t1 (col1 number);

    Table created.

    SQL> create unique index i1_t1 on t1 (col1) ;

    Index created.

    SQL> alter table t1 add constraint u1_t1 unique(col1);

    Table altered.

    SQL> select index_name from user_ind_columns where table_name='T1';

    INDEX_NAME
    ------------------------------
    I1_T1

    SQL> alter table t1 drop constraint u1_t1;

    Table altered.

    SQL> select index_name from user_ind_columns where table_name='T1';

    INDEX_NAME
    ------------------------------
    I1_T1

    SQL>
  • 7. Re: Unique constraint and index relationship
    Dom Brooks Guru
    Currently Being Moderated
    If you are not the owner of index then oracle does not drops it.
    sure, of course.
  • 8. Re: Unique constraint and index relationship
    760565 Explorer
    Currently Being Moderated
    I couldn't implement the same thing as you did. I "copy pasted" your commands and run them. But at the end my index wasn't dropped. My version is 10.2
  • 9. Re: Unique constraint and index relationship
    Dom Brooks Guru
    Currently Being Moderated
    Hence what Sybrand was saying about version being all important.

    See KEEP INDEX clause of DROP CONSTRAINT
  • 10. Re: Unique constraint and index relationship
    760565 Explorer
    Currently Being Moderated
    An extract from docs

    http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/clauses002.htm#g1053592
    When you drop a primary key constraint:
    
    If the primary key was created using an existing index, then the index is not dropped.
    
    If the primary key was created using a system-generated index, then the index is dropped.
    How did you manage to have it dropped.
  • 11. Re: Unique constraint and index relationship
    839468 Newbie
    Currently Being Moderated
    On Oracle 11.2.0.1.0
    I did following things -
    1. Unique index does not exists on column and created unique constraint.
    2. Unique index exists and create unique constraint

    I don't find any difference between two scenarios in all_constraints table. Actually, I wanted to know how Oracle decides that constraint is not the owner of unique index in case 2 and retains it. And in case 1 drops index along with constraint.

    SQL> create table test1 (id nvarchar2(10));

    Table created.

    SQL> alter table test1 add constraint u1_test1 unique(id);

    Table altered.

    SQL> select constraint_name, index_name, index_owner from all_constraints where TABLE_NAME='TEST1';

    CONSTRAINT_NAME INDEX_NAME
    ------------------------------ ------------------------------
    INDEX_OWNER
    ------------------------------
    U1_TEST1 U1_TEST1


    SQL> alter table test1 drop constraint u1_test1;

    Table altered.

    SQL> select index_name, index_owner, constraint_name, delete_rule from dba_constraints where TABLE_NAME='TEST1';

    no rows selected

    SQL> create unique index i1_test1 on test1(id);

    Index created.

    SQL> alter table test1 add constraint u1_test1 unique(id);

    Table altered.

    SQL> select constraint_name, index_name, index_owner from all_constraints where TABLE_NAME='TEST1';

    CONSTRAINT_NAME INDEX_NAME
    ------------------------------ ------------------------------
    INDEX_OWNER
    ------------------------------
    U1_TEST1 I1_TEST1


    SQL>

    Edited by: 836465 on Feb 14, 2011 4:00 AM
  • 12. Re: Unique constraint and index relationship
    Dom Brooks Guru
    Currently Being Moderated
    Trace the session - you will see the recursive SQL.
  • 13. Re: Unique constraint and index relationship
    AKPT Newbie
    Currently Being Moderated
    Hi,
    I your comment you have mentioned :-

    If you are not the owner of index then oracle does not drops it.

    Kindly elaborate your comment .
    I'm confused with this and above example.

    Thanks,
    Alok
  • 14. Re: Unique constraint and index relationship
    Paul Horth Expert
    Currently Being Moderated
    user12141893 wrote:
    Hi,
    I your comment you have mentioned :-

    If you are not the owner of index then oracle does not drops it.

    Kindly elaborate your comment .
    I'm confused with this and above example.

    Thanks,
    Alok
    Please open your own thread on the subject rather than resurrect a year-old post.

Legend

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