14 Replies Latest reply: Sep 20, 2012 5:00 AM by Paul Horth RSS

    Unique constraint and index relationship

    839468
      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
          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
            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
              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
                DimaK
                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
                  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
                    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
                      If you are not the owner of index then oracle does not drops it.
                      sure, of course.
                      • 8. Re: Unique constraint and index relationship
                        DimaK
                        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
                          Hence what Sybrand was saying about version being all important.

                          See KEEP INDEX clause of DROP CONSTRAINT
                          • 10. Re: Unique constraint and index relationship
                            DimaK
                            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
                              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
                                Trace the session - you will see the recursive SQL.
                                • 13. Re: Unique constraint and index relationship
                                  AKPT
                                  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
                                    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.