Question :
The index are on tuples, ordered sets, of columns, but the UNIQUE constraint or qualifiant for an index is on (unordered) sets of columns.
I have MySQL/MariaDB in mind but my questions certainly applies to other DBMS.
If I have a table T with two columns id1, id2 and two index on this two columns:
INDEX idx_id1_id2 (id1, id2)
INDEX idx_id2_id1 (id2, id1)
and I want to declare to the DBMS that the multi-set on all records of the table T of all couples (value(id1), value(id2)), or equivalently all couples (value(id2), value(id1)), is “UNIQUE” (this multi-set is a set),
I can change
INDEX idx_id1_id2 (id1, id2)
INDEX idx_id2_id1 (id2, id1)
for
UNIQUE INDEX idx_id1_id2 (id1, id2)
INDEX idx_id2_id1 (id2, id1)
But I can also declare :
UNIQUE INDEX idx_id1_id2 (id1, id2)
UNIQUE INDEX idx_id2_id1 (id2, id1)
Is there some DBMS that deduce from
UNIQUE INDEX idx_id1_id2 (id1, id2)
INDEX idx_id2_id1 (id2, id1)
that the second index is also unique?
Is there some DBMS that deduce from
UNIQUE INDEX idx_id1_id2 (id1, id2)
UNIQUE INDEX idx_id2_id1 (id2, id1)
that they don’t have to do twice the unicity check on inserting/updating value in the table ?
From a declarative point of view, I prefer
UNIQUE INDEX idx_id1_id2 (id1, id2)
UNIQUE INDEX idx_id2_id1 (id2, id1)
but however there may be a performance penalty on insert/update.
If there is a performance penalty for declaring UNIQUE on many indexes, will it be significant ?
Is there any (significant) performance boost for some queries with the second UNIQUE qualifiant added ?
Answer :
Case 1 If you need to avoid both (1,2) and (2,1), then nothing in the Question will prevent that form of “dup”.
To do that, always store the LESSER() value in id1 and the GREATER() in id2. Then UNIQUE(id1, id2) prevents dups.
Meanwhile, do a similar sort of the two values when doing a lookup.
Then you have only the one UNIQUE key — which may as well be the PRIMARY KEY. Now you have
- Uniqueness constraint that works
- Fewer indexes
- Only need to do one lookup, not two
etc
Case 2 If (1,2) and (2,1) can coexist, then the order of the ids presented to the table is important. Probably all you need is
PRIMARY KEY(id1, id2)
If you have something like WHERE id1 BETWEEN .. AND .. AND id2 = 7, then you would also need
INDEX(id2, id1)
My Cookbook covers most of these simply possibilities.
Yes you should. Index for unique data need qualifier UNIQUE because that determine which kind of tree structure will be used. Sure you can achieve uniqness of fields combination by single index of UNIQUE type but unique index itself is somehow faster than non-unique one. If an index can be unique or non-unique – make it unique.