When to use a Unique constraints vs unique indices on Oracle?

Question

Is there any kind of good practice and/or recommendation when creating unique vs indices constraints on the Oracle?

When I should wear one and when I should wear the other?

Is there a situation where I should use both?

ALTER TABLE t ADD CONSTRAINT t_c1_c2_unq UNIQUE (c1, c2);
CREATE UNIQUE INDEX t_c1_c2_unq_idx ON t(c1, c2);

Answer 1

Oneness of data

To ensure the uniqueness of the data, there is no practical difference in most cases. A Unique Constraint is almost always implemented as a Unique index, and a Unique index prevents you from having repeated values (thus functioning as a Unique Constraint).

It is recommended, however, that whenever you need an index for performance issues, you explicitly create the index (to cover cases where the index is not created by Constraint).

Foreign key

Finally, if you want to reference a column (or set of columns) in a foreign key, that column(s) should(m) have a Unique Constraint. In the example below, the second foreign key cannot be created because it references a column that does not have a Unique Constraint (despite having a unique index).

CREATE TABLE cons(
  id NUMBER, 
  CONSTRAINT cons_cons UNIQUE (id));

CREATE TABLE ind(id NUMBER);
CREATE UNIQUE INDEX ind_ind on ind(id);

-- OK. Chave estrangeira referencia coluna
-- com CONSTRAINT UNIQUE.
CREATE TABLE fk_cons(
  fk NUMBER, 
  CONSTRAINT fk1 FOREIGN KEY(fk) REFERENCES cons(id));

-- Erro: para ser referenciado por uma chave estrangeira,
-- ind(id) deve ser PRIMARY KEY ou ter CONSTRAINT UNIQUE.
CREATE TABLE fk_ind(
  fk NUMBER, 
  CONSTRAINT fk2 FOREIGN KEY(fk) REFERENCES ind(id));

Sources: https://forums.oracle.com/thread/1033157 and https://stackoverflow.com/questions/7521817/oracle-unique-constraint-and-unique-index-question