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

Question

Liquibase has two ways to identify a column as unique:

• When creating the table, use <constraints> in the column:

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

• 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 a difference between the two approaches for unique keys with one column?

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

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

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

Reference Information. I have a script that built the linibase change log directly from my relational model in code. The script generation generated BOTH declarations if the model indicated that the column is unique. I am clearing the generated results and want to delete one of the ads and want to know if this is suitable.

Answer 1

See PostgreSQL Documentation:

Note. Preferred Adding Method A unique constraint on the table is ALTER TABLE ... ADD CONSTRAINT. Using indexes to provide unique constraints can be seen as implementing parts that should not be addressed directly. It should, however, be aware that there is no need to manually create indexes on unique columns; this will simply duplicate the automatically generated index.

Thus, a unique constraint is a concept that is implemented (in PostgreSQL) with a unique index.

Answer 2

I think it depends on the target DBMS.

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

The only restriction may be the purpose of limiting the foreign key, but the unique index cannot. I don't know if this is the same as MySQL.