Liquibase unique constarint is not applied for a column named 'name' but works for any other columns

Question

I wanna make two columns unique and i've created changelist with two addUniqueConstraint's cause when i try to list required columns within one addUniqueConstraint columnNames section it ins't worked.

So, i've specified addUniqueConstraint for "setSpec" column and second for "name" column, but constraint is applied only for "setSpec" column and i still have possibility to insert entity with duplicated name value. Also i've tried to rename column from "name" to "set_name" but behaviour has left the same.

So now i am confused about making both columns unique. Can anybady suggest a solution or maybe point out to my mistake cause i dont think such big library can have such simple defects. Thank you. But here is an interesting thing, when i leave only one addUniqueConstraint section then any one of specified columns get the constraint.

Here is my table:

<changeSet id="2020-07-07--16-00-create-set-table" author="Illia Daliek">
    <createTable tableName="set">
      <column name="id" type="uuid">
        <constraints primaryKey="true" nullable="false"/>
      </column>
      <column name="name" type="varchar(255)">
        <constraints nullable="false"/>
      </column>
      <column name="description" type="varchar(1024)"/>
      <column name="setSpec" type="varchar(255)">
        <constraints nullable="false"/>
      </column>
      <column name="created_by_user_id" type="uuid"/>
      <column name="created_date" type="timestamptz"/>
      <column name="updated_by_user_id" type="uuid"/>
      <column name="updated_date" type="timestamptz"/>
    </createTable>
  </changeSet>

here is my try to add unique constraints

  <changeSet id="rename-column" author="Illia Daliek">
    <renameColumn tableName="set" oldColumnName="name" newColumnName="set_name"/>
  </changeSet>

  <!--this one works fine-->
  <changeSet  id="2020-08-28--11-00-add-unique-constraint-to-set.set_spec-column " author="Illia Daliek">
    <addUniqueConstraint  columnNames="setSpec"
                          constraintName="unique"
                          schemaName="${database.defaultSchemaName}"
                          tableName="set"/>
  </changeSet>

  <!--this one doesn't work-->
  <changeSet  id="2020-08-28--11-00-add-unique-constraint-to-set.name-column " author="Illia Daliek">
    <addUniqueConstraint  columnNames="set_name"
                          constraintName="unique"
                          schemaName="${database.defaultSchemaName}"
                          tableName="set"/>
  </changeSet>

Note that two unique constraints on one column each is something different than one unique constraint on two columns. But `columnNames="setspec, name"` should work to create a constraint on two columns — , Aug 28 '20 at 13:48

martinspielmann · Accepted Answer · 2020-08-30T07:23:34.097

1

Give another constraintName to the second constraint. I don't think you can have two contraints with identical Names on a table

edited Aug 30 '20 at 07:23

answered Aug 28 '20 at 13:50

martinspielmann

536
6
19

Thank you for answer but this didn't help – Илья Илья Aug 30 '20 at 05:23
Hmm, ok. It doesn't hurt either: https://stackoverflow.com/questions/1397671/can-there-be-constraints-with-the-same-name-in-a-db#:~:text=two%20different%20schemas%20in%20the,containing%20schema%20unless%20specified%20otherwise.&text=Index%20names%20must%20follow%20the%20rules%20of%20identifiers. Is there any error log you could share? – martinspielmann Aug 30 '20 at 07:21

Liquibase unique constarint is not applied for a column named 'name' but works for any other columns

1 Answers1