ADD UNIQUE USING INDEX - Oracle to MariaDB Migration

In Oracle, you can add a unique constraint to a table using an existing unique index on the same columns.

In MariaDB, you cannot specify an existing index when adding a unique constraint, and if a unique index already exists, and you add a unique constraint, MariaDB will create a duplicate unique index.

Oracle:

  CREATE TABLE unique1 (c1 VARCHAR2(10));
  /* Table created. */
 
  -- Add unique index
  CREATE UNIQUE INDEX idx1 ON unique1 (c1);
  /* Index created. */
 
  -- Add unique constraint using the existing index
  ALTER TABLE unique1 ADD UNIQUE (c1) USING INDEX idx1; 
  /* Table altered. */

MariaDB - Oracle Compatibility:

  CREATE TABLE unique1 (c1 VARCHAR(10));
  /* Query OK, 0 rows affected */
 
  -- Add unique index
  CREATE UNIQUE INDEX idx1 ON unique1 (c1);
  /* Query OK, 0 rows affected */
 
  -- Add unique constraint 
  ALTER TABLE unique1 ADD UNIQUE (c1); 
  /* Query OK, 0 rows affected, 1 warning */
 
  SHOW WARNINGS;
  /* Note  | 1831 | Duplicate index `c1`. 
      This is deprecated and will be disallowed in a future release */

You can see that two indexes were created:

MariaDB - Oracle Compatibility:

  SHOW CREATE TABLE unique1;
  /* CREATE TABLE `unique1` (
        `c1` varchar(10) DEFAULT NULL,
        UNIQUE KEY `idx1` (`c1`),
        UNIQUE KEY `c1` (`c1`)
      ) ... */
 
   SHOW INDEXES FROM unique1;
   /* | Table     | Non_unique | Key_name | Seq_in_index | Column_name | ...
       | unique1 |                0  | idx1         |                   1 | c1                  | ...
       | unique1 |                0  | c1            |                   1 | c1                  | .... */

The tool comments out the CREATE UNIQUE INDEX statement if both a unique index and unique constraint exist on the same columns. This gives priority to the constraint and prevents the creation of a duplicate index.

For more information, see Oracle to MariaDB Migration - Oracle Compatibility Mode.