MySQL: unique and index equivalence

I created a Java application that uses Hibernate ORM, with Hibernate tools I get an automated script that install or upgrades the DB schema from the Java objects used as entities.

The program works properly in MySQL, however for Oracle an error is triggered when in one column the constraint "unique" is declared and after an index is attempted to be defined. Oracle says that a "unique" constraint creates an index by default, so two indexes on the same column cannot be declared,

So, my question is if in MySQL there's an equivalence or relation between the unique constraint and one index.

开发者_开发技巧

Please clarify. Thanks in advanced.

---

A unique constraint requires a index so it can be enforced. Both DBMS create an appropriate index when you declare columns as unique. The only difference is that Oracle prevents you from creating redundant indexes but MySQL doesn't:

show index from test_table;

+------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table      | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| test_table |          0 | PRIMARY    |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| test_table |          0 | foo_unique |            1 | foo         | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| test_table |          1 | foo_key    |            1 | foo         | A         |           0 |     NULL | NULL   |      | BTREE      |         |
+------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

---

MySQL just doesn't care for useless indexes. For updates it will check all unique indexes and for SELECTs it will pick an arbitrary index.

So to make Oracle happy, delete the index before creating the unique index.