CODEWITHSUNDEEP
oracle-databaseconstraintsunique-constraint
Jonny Shanahan
Jonny Shanahan

Reputation: 421

Why unique index AND unique constraint (multi columns)?

So a table I am looking at has a unique constraint AND a unique index over multiple columns, and the exact same columns for both.

Is there a use for this or is the unique constraint redundant?

Upvotes: 1

Views: 1341

Answers (1)

Jon Heller
Jon Heller

Reputation: 36808

I agree that the existence of unique constraints and unique indexes does look redundant at first. It seems like a violation of Don't Repeat Yourself, allowing for confusing differences. But there are at least two reasons both exist - management features and allowing existing duplicates.

Management Features

In theory, a logical constraint can be created without worrying about the implementation. The constraint specifies what must be true, along with some options such as deferring the constraint until a commit.

In practice, constraints have such a large performance and storage penalty that the implementation must be considered. An index is required or else a single insert would require O(n) time instead of O(log(n)). Those indexes can take up a huge amount of space; someone might want to specify where it's stored, how it's compressed, etc.

Most of the time those features aren't important and using all the index defaults is fine. But sometimes storage and performance are critical and people will want to tweak the index without caring about the constraint.

Allow Existing Duplicates

There is at least one case where a unique constraint does not have a unique index. It's possible to allow existing duplicate valuess but prevent any future duplicates by setting the constraint to NOVALIDATE and using a non-unique index.

--Create table and insert duplicate values.
create table test1(a number);

insert into test1 values(1);
insert into test1 values(1);
commit;

--Add a non-validated unique constraint, with a non-unique index.
alter table test1
add constraint test1_uq unique(a)
using index (create /* Not unique!*/ index test1_uq on test1(a)) novalidate;

--Now multiple inserts raise: ORA-00001: unique constraint (JHELLER.TEST1_UQ) violated
insert into test1 values(2);
insert into test1 values(2);

The physical index must allow duplicates, but the logical constraint knows to not allow any more duplicates. Although this is a rare feature and I'm not sure if I've ever seen it in production code.

Upvotes: 1

Related Questions