4 Replies Latest reply on Feb 18, 2010 10:08 AM by 748490

    Unique Index vs. Unique Constraint

    748490
      Hi All,

      I'm studying for the Oracle SQL Expert Certification. At one point in the book, while talking about indices, the author says that a unique index is not the same a unique constraint. However, he doesn't explain why they're two different things.

      Could anyone clarify the difference between the two, please?

      Thanks a lot,

      Valerio
        • 1. Re: Unique Index vs. Unique Constraint
          641320
          The main difference is if you need to reference the unique constrainted column with some foreign key. For that purpose you need unique CONSTRAINT, unique INDEX will not be enough.

          So for ensuring uniqueness both methods will do the same job. but if you need a foreign key referencing that unique column (or combination of columns) you need UNIQUE KEY constraint.

          S
          • 2. Re: Unique Index vs. Unique Constraint
            Boneist
            A constraint has different meaning to an index. It gives the optimiser more information and allows you to have foreign keys on the column, whereas a unique index doesn't.

            eg:
            SQL> create table t1 (col1 number, col2 varchar2(20), constraint t1_uq unique (col1));
            
            Table created.
            
            SQL> create table t2 (col1 number, col2 varchar2(20));
            
            Table created.
            
            SQL> create unique index t2_idx on t2 (col1);
            
            Index created.
            
            SQL> create table t3 (col1 number, col2 number, col3 varchar2(20), constraint t3_fk
              2                   foreign key (col2) references t1 (col1));
            
            Table created.
            
            SQL> create table t4 (col1 number, col2 number, col3 varchar2(20), constraint t4_fk
              2                   foreign key (col2) references t2 (col1));
                             foreign key (col2) references t2 (col1))
                                                               *
            ERROR at line 2:
            ORA-02270: no matching unique or primary key for this column-list
            It's like saying "What's the difference between a car seat and an armchair? They both allow you to sit down!"
            • 3. Re: Unique Index vs. Unique Constraint
              747100
              Now perhaps one should mention that the time of the uniqueness enforcement may be different if we declare the unique constraint to be deferred....
              • 4. Re: Unique Index vs. Unique Constraint
                748490
                Ok, now everything is much clearer. Thanks to everyone for the answers.