Is there some kind of good practice and / or recommendation when creating unique indexes vs. constraints in Oracle?
When should I use one and when should I use 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);
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 working as < in> unique constraint ).
It is recommended, however, that whenever you need an index for performance issues, you explicitly create index (to cover cases where the index is not created by constraint ).
Finally, if you want to reference a column (or set of columns) in a foreign key, that column (s) must have a unique constraint . In the example below, the second foreign key can not be created because it references a column that does not have a unique constraint (although it has 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));