9

Is it possible to add a constraint like

ALTER TABLE `t1` ADD UNIQUE(`col1`, `col2`);

without creating an index? The index wouldn't be used for any queries so it would be a waste of space.

It wouldn't be a problem if inserts and updates would be way slower, because the table doesn't get updated very often.

COMMANDER CAPSLOCK
  • 316
  • 3
  • 8
  • 2
    http://stackoverflow.com/questions/9764120/does-a-unique-constraint-automatically-create-an-index-on-the-fields – Jason Heo Nov 11 '13 at 13:55
  • Thanks. I had an index on each column. With the link provided i learned that i won't need the col1 index if there is an unique constraint, so i could at least drop that one. Preventing duplicates by unique indexing the columns still "feels" like a workaround though. – COMMANDER CAPSLOCK Nov 11 '13 at 14:25

1 Answers1

14

No, this is not possible. A UNIQUE constraint contains an index definition and I barely imagine how it might be implemented without creating an index (in DBMS terms).

You should realize that indexes are not just 'wizardy' - they are a real data structure, which takes space to be placed, special procedures to be handled e.t.c. A unique constraint, itself, means unique index values, not unique column values.

Ben
  • 51,770
  • 36
  • 127
  • 149
Alma Do
  • 37,009
  • 9
  • 76
  • 105
  • 1
    To add to this, it seems like if the DB is going to have to guarantee/protect uniqueness of a column, it would need quick access to all the values in that column, to check for existing values on an insert or update. I'm no DBMS expert by any stretch, but an index would supply that quick access. – xdhmoore Aug 06 '14 at 15:36
  • 1
    Not completely so, but you have the point. Index is needed because of _how_ uniqueness could be reached (and that is checking B-tree for which index is needed - if we're speaking about B-tree index) – Alma Do Aug 06 '14 at 15:50