11 Replies Latest reply: Feb 3, 2007 5:49 PM by William Robertson RSS

    Unique Key, Foreign Key and Associated Index

    Avi Abrami
      Greetings,
      If a column in a database is defined as a unique key and also as a foreign key, should it have a unique index associated with it or a non-unique index?

      Thanks,
      Avi.
        • 1. Re: Unique Key, Foreign Key and Associated Index
          orawarebyte
          Avi it would be shot in the dark,but AFAIK it would be unique index
          SQL> CREATE TABLE t1 (a  NUMBER PRIMARY KEY)
            2  /
          
          Table created.
          
          SQL> CREATE TABLE t2 (b   NUMBER UNIQUE)
            2  /
          
          Table created.
          
          SQL> ALTER TABLE t2 ADD CONSTRAINT t2_b_fk FOREIGN KEY (b) REFERENCES t1 (a)
            2  /
          
          Table altered.
          
          
          SQL> INSERT INTO t1 VALUES (1)
            2  /
          
          1 row created.
          
          SQL> INSERT INTO t2 VALUES (1)
            2  /
          
          1 row created.
          
          SQL> /
          INSERT INTO t2 VALUES (1)
          *
          ERROR at line 1:
          ORA-00001: unique constraint (SCOTT.SYS_C00109079) violated
          
          
          
          SQL> EXPLAIN PLAN FOR SELECT b FROM t2
            2  /
          
          Explained.
          
          SQL> SELECT * FROM TABLE(dbms_xplan.display)
            2  /
          
          PLAN_TABLE_OUTPUT
          ----------------------------------------------------------------------------------------------------------
          Plan hash value: 1781207681
          
          --------------------------------------------------------------------------
          | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
          --------------------------------------------------------------------------
          |   0 | SELECT STATEMENT  |      |     1 |    13 |     3   (0)| 00:00:01 |
          |   1 |  TABLE ACCESS FULL| T2   |     1 |    13 |     3   (0)| 00:00:01 |
          --------------------------------------------------------------------------
          
          Note
          -----
             - dynamic sampling used for this statement
          
          12 rows selected.
          
          SQL> DELETE FROM plan_table
            2  /
          
          2 rows deleted.
          
          SQL> COMMIT;
          
          Commit complete.
          
          SQL> EXPLAIN PLAN FOR SELECT b FROM t2 WHERE b=1;
          
          Explained.
          
          SQL> SELECT * FROM TABLE(dbms_xplan.display)
            2  /
          
          PLAN_TABLE_OUTPUT
          ----------------------------------------------------------------------------------------------------------
          Plan hash value: 4009956835
          
          -----------------------------------------------------------------------------------
          | Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
          -----------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT  |               |     1 |    13 |     1   (0)| 00:00:01 |
          |*  1 |  INDEX UNIQUE SCAN| SYS_C00109079 |     1 |    13 |     1   (0)| 00:00:01 |
          -----------------------------------------------------------------------------------
          
          Predicate Information (identified by operation id):
          ---------------------------------------------------
          
             1 - access("B"=1)
          
          13 rows selected.
          Correct me if m wrong.

          Khurram
          • 2. Re: Unique Key, Foreign Key and Associated Index
            MichaelS
            Not neccessarily: Here is an example where a non-unique index is associated with a unique key: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:8743855576462#19069597216080
            • 3. Re: Unique Key, Foreign Key and Associated Index
              orawarebyte
              As per above scenario 
              
              SQL> SELECT index_name,uniqueness FROM user_indexes WHERE table_name='T2'
                2  /
              
              INDEX_NAME                     UNIQUENES
              ------------------------------ ---------
              SYS_C00109079                  UNIQUE
              Khurram
              • 4. Re: Unique Key, Foreign Key and Associated Index
                398653
                CASE-1
                CREATE TABLE MASTER
                (c1 NUMBER CONSTRAINT MASTER_C1_PK PRIMARY KEY );
                 
                 
                CREATE TABLE DETAIL
                (c1 NUMBER UNIQUE);
                 
                 
                ALTER TABLE  DETAIL
                ADD CONSTRAINT DETAIL_C1_FK FOREIGN KEY (c1) REFERENCES MASTER(c1);
                 
                 
                SELECT OWNER, INDEX_NAME, UNIQUENESS
                FROM   dba_indexes
                WHERE  table_name = 'DETAIL'
                AND    owner = 'SCOTT';
                 
                 
                INSERT INTO MASTER(C1)
                VALUES(1);
                 
                INSERT INTO DETAIL(C1)
                VALUES(1);
                 
                INSERT INTO DETAIL(C1)
                VALUES(1);
                 
                CASE#1 RESULT : In this Case UNIQUE CONSTRAINT will be Violated and DBA_INDEXES will Show the Index as UNIQUE.

                CASE-2
                CREATE TABLE TEST
                (C1 NUMBER(10));
                 
                CREATE INDEX TEST_INDX ON TEST(C1);
                 
                SELECT INDEX_NAME, UNIQUENESS
                FROM   dba_indexes
                WHERE  table_name = 'TEST'
                AND    OWNER = 'SCOTT';
                 
                ALTER TABLE TEST
                ADD CONSTRAINT T1_PK PRIMARY KEY(C1);
                 
                SELECT INDEX_NAME, UNIQUENESS
                FROM   dba_indexes
                WHERE  table_name = 'TEST'
                AND    OWNER = 'SCOTT';
                 
                INSERT INTO TEST(C1)
                VALUES(1);
                 
                INSERT INTO TEST(C1)
                VALUES(1);
                CASE#2 RESULT : In this Case PRIMARY KEY CONSTRAINT will be Violated and DBA_INDEXES will Show the Index as NONUNIQUE.

                CASE-3
                CREATE TABLE TAB1
                (C1 NUMBER);
                 
                CREATE INDEX TAB1_INDX ON TAB1(C1);
                 
                SELECT INDEX_NAME, UNIQUENESS
                FROM   dba_indexes
                WHERE  table_name = 'TAB1';
                 
                ALTER TABLE TAB1
                ADD CONSTRAINT UNQ_INDX UNIQUE(C1);
                 
                SELECT INDEX_NAME, UNIQUENESS
                FROM   dba_indexes
                WHERE  table_name = 'TAB1';
                 
                INSERT INTO TAB1(C1)
                VALUES(1);
                 
                INSERT INTO TAB1(C1)
                VALUES(1);
                CASE#3 RESULT : In this Case, UNIQUE CONSTRAINT will be Violated and DBA_INDEXES will Show the Index as "NONUNIQUE".

                Thanks & Regards

                Muhammad Waseem Haroon
                waseemharoon@gmail.com
                mwaseem_haroon@yahoo.com
                ocp_waseem@hotmail.com
                • 5. Re: Unique Key, Foreign Key and Associated Index
                  MichaelS
                  I can't really verifiy! Why do we have different results?:
                  SQL> CREATE TABLE t (a INTEGER)
                  Table created.
                  SQL> CREATE INDEX t_idx ON t (a) COMPUTE STATISTICS
                  Index created.
                  SQL> ALTER TABLE t ADD
                  CONSTRAINT t_pk
                  PRIMARY KEY (a)
                  Table altered.
                  SQL> INSERT INTO t
                     SELECT ROWNUM
                       FROM user_objects
                  16244 rows created.

                  SQL> SELECT *
                    FROM t
                  WHERE a = 1

                           A
                  ----------
                           1


                  1 row selected.

                  Execution Plan
                  ----------------------------------------------------------
                     0       SELECT STATEMENT Optimizer Mode=CHOOSE
                     1    0    INDEX RANGE SCAN EM_DEVELOP.T_IDX

                  SQL> SELECT index_name, uniqueness
                    FROM user_indexes
                  WHERE table_name = 'T'

                  INDEX_NAME                     UNIQUENES
                  ------------------------------ ---------
                  T_IDX                          NONUNIQUE
                  • 6. Re: Unique Key, Foreign Key and Associated Index
                    398653
                    Just change your statement.
                    CREATE UNIQUE INDEX t_idx ON t (a) COMPUTE STATISTICS;
                    and see the result. :)

                    Thanks & Regards

                    Muhammad Waseem Haroon
                    waseemharoon@gmail.com
                    mwaseem_haroon@yahoo.com
                    ocp_waseem@hotmail.com
                    • 7. Re: Unique Key, Foreign Key and Associated Index
                      William Robertson
                      Regarding the original question, did you mean "what is the database behaviour when you create a unique constraint?" or "what would be best practice here?" You can use either a unique or a nonunique index for a unique constraint. I'm not aware of any special considerations if the column is also a foreign key.
                      • 8. Re: Unique Key, Foreign Key and Associated Index
                        Avi Abrami
                        William,
                        Thanks for your relevant reply.
                        I wanted to know the best practice.

                        Cheers,
                        Avi.
                        • 9. Re: Unique Key, Foreign Key and Associated Index
                          MichaelS
                          It was my purpose to not explicitly use a unique index, to show that a non-unique index can be used to enforce a primary key (or unique) constraint (as you already showed yourself ;))
                          As William also mentioned, there's no real »best practice« which kind of index should be used. Either work well as already mentioned by Tom Kyte as well:
                          http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:8743855576462#76948622206088
                          • 10. Re: Unique Key, Foreign Key and Associated Index
                            Muthukumar S
                            It would be UNIQUE INDEX.

                            There might be some scenario like a table is a subset of other table (might not be good design).
                            In that case the relationship would be 1-1 or 1-0
                            Example - you have employee table and personal info master table.
                            Employee table of that org might have records of all employees including contractors (minimum info) whereas the personal_info table might be maintained for their permanent employees alone (might be more fields).

                            In this scenario employee table might have emp_id as primary key and personal_info would have emp_id as foreign key - still we could not allow multiple entries of same emp id in Personal_info - in such case you might go for Unquie key for that column in personal_info.

                            In such case Unique index would be associated.

                            Regards,
                            S.Muthukumar.
                            • 11. Re: Unique Key, Foreign Key and Associated Index
                              William Robertson
                              > In such case Unique index would be associated.

                              A unique constraint, not necessarily a unique index.

                              A nonunique index may give more flexibility, for example allowing a deferred constraint.