This content has been marked as final. Show 4 replies
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.
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.
It's like saying "What's the difference between a car seat and an armchair? They both allow you to sit down!"
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
Now perhaps one should mention that the time of the uniqueness enforcement may be different if we declare the unique constraint to be deferred....
Ok, now everything is much clearer. Thanks to everyone for the answers.