With Liquibase, is there a difference between using a unique <createIndex> and using <column> with a unique constraint?

Question

Liquibase has two ways to define a column as unique:

1. When creating the table, using <constraints> on the column:

   <createTable tableName="my_table">
       <column name="my_column">
           <constraints unique="true"
                        uniqueConstraintName="my_table_my_column_uk">
       </column>
   </createTable>
   

2. After creating the table, using <createIndex>:

   <createTable tableName="my_table">
       <column name="my_column"/>
   </createTable>
   <createIndex tableName="my_table" unique="true"
                indexName="my_table_my_column_uk">
       <column name="my_column"/>
   </createIndex>
   

Is there any difference between these two approaches for single-column unique keys?

In my own observations with MySQL, there seems to be no difference. Both declarations (above) yield the same SHOW CREATE TABLE result:

...
UNIQUE_KEY `my_table_my_column_uk` (`my_column`)
...

However, does this hold true for all database implementations, or does <createIndex unique="true"> generate different schema output from <constraint unique="true"/> for different databases?

Background: I have a script that has built the liquibase changelog directly from my relational model in the code. The generation script created BOTH declarations if the model indicated the column was unique. I'm cleaning up the generated results and would like to remove one of the declarations, and want to know if that's appropriate.

Series

Answer 1

See PostgreSQL Documentation:

Note: The preferred way to add a unique constraint to a table is ALTER TABLE ... ADD CONSTRAINT. The use of indexes to enforce unique constraints could be considered an implementation detail that should not be accessed directly. One should, however, be aware that there's no need to manually create indexes on unique columns; doing so would just duplicate the automatically-created index.

So a unique constraint is a concept which is implemented (in PostgreSQL) with a unique index.

Dorelle

Answer 2

I guess it depends on the target DBMS.

In PostgreSQL, Oracle and DB2 there is a differences between a unique index and an unique constraint.

The unique constraint can be the target of a foreign key constraint but the unique index cannot. Don't know if that is the same with MySQL.

Shenyang

I don't believe I'll be setting up any foreign keys that reference unique keys, so that shouldn't be a problem. But thanks for the information; I'll be doing some additional searching w.r.t unique keys/constraints on different databases to see if my changes would be problematic. – Series