Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Unique Index vs. Unique Constraint

748490Feb 18 2010 — edited Feb 18 2010
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
This post has been answered by Boneist on Feb 18 2010
Jump to Answer

Comments

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
Boneist
Answer
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!"
Marked as Answer by 748490 · Sep 27 2020
1 person found this helpful
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....
748490
Ok, now everything is much clearer. Thanks to everyone for the answers.
1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Mar 18 2010
Added on Feb 18 2010
4 comments
58,939 views