magento2: duplicate key name while adding indexes and constraints in db_schema.xml

Expanding on #27829 I bumped into a bug here that I can’t find a way to work around.

Preconditions (*)

Magento 2.3.4

Steps to reproduce (*)

Create a new module
In db_schema.xml add the following declarations in a new table

  <table name="mytable" resource="default" engine="innodb">
       <column xsi:type="int" name="entity_id" identity="true" unsigned="true" nullable="false" comment="Entity Id" />
       <column xsi:type="varchar" length="100" name="identifier" nullable="false" comment="Identifier" />
       <column xsi:type="timestamp" name="created_at" nullable="false" comment="Creation Time" default="CURRENT_TIMESTAMP" on_update="false" />
       <column xsi:type="timestamp" name="updated_at" nullable="false" comment="Update Time" default="CURRENT_TIMESTAMP" on_update="true" />
       <!-- // Add more static attributes here...-->
       <constraint xsi:type="primary" referenceId="PRIMARY">
           <column name="entity_id" />
       </constraint>
       <index referenceId="IDENTIFIER" indexType="btree">
           <column name="identifier" />
       </index>
   </table>

Execute bin/magento setup:db-schema:upgrade
Modify XML like this

    <table name="mytable" resource="default" engine="innodb">
        <column xsi:type="int" name="entity_id" identity="true" unsigned="true" nullable="false" comment="Entity Id" />
        <column xsi:type="varchar" length="100" name="identifier" nullable="false" comment="Identifier" />
        <column xsi:type="timestamp" name="created_at" nullable="false" comment="Creation Time" default="CURRENT_TIMESTAMP" on_update="false" />
        <column xsi:type="timestamp" name="updated_at" nullable="false" comment="Update Time" default="CURRENT_TIMESTAMP" on_update="true" />
        <!-- // Add more static attributes here...-->
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="entity_id" />
        </constraint>
        <index referenceId="IDENTIFIER" indexType="btree">
            <column name="identifier" />
        </index>
        <constraint xsi:type="unique" referenceId="UNIQUE">
           <column name="identifier" />
        </constraint>
    </table>

Execute bin/magento setup:db-schema:upgrade

Expected result (*)

The indexes are created

Actual result (*)

SQLSTATE[42000]: Syntax error or access violation: 1061 Duplicate key name 'MYTABLE_IDENTIFIER'

SQLSTATE[42000]: Syntax error or access violation: 1061 Duplicate key name 'MYTABLE_IDENTIFIER', query w as: ALTER TABLEmytableADD INDEXMYTABLE_IDENTIFIER (identifier)

Basically Magento is unable to generate a unique name for the index and the constraint.

About this issue

Original URL
State: closed
Created 4 years ago
Reactions: 4
Comments: 17 (5 by maintainers)

Most upvoted comments

Confirmed issues can’t be closed automatically.

dverkade on Mar 17, 2021

Yes exactly that’s the point of the bug. While in mariadb you can define both indexes at the same time with a different name, with db_schema.xml you can’t because it’s reusing the same referenceId, it can’t create a unique one for both at the same time because the algorithm that creates the names cannot create a unique name for both.

ioweb-gr on Apr 27, 2020

@ioweb-gr You can add one index for a column at a time. You can either add INDEX or UNIQUE for a column.

For adding UNIQUE constraint You have to first drop index then add a UNIQUE constraint fo that column The query will be like below:

ALTER TABLE mytable DROP INDEX MYTABLE_IDENTIFIER, ADD UNIQUE MYTABLE_IDENTIFIER (identifier) USING BTREE;

With the help of disabled=" true" You can add UNIQUE constraint.

 <table name="mytable" resource="default" engine="innodb">
      <column xsi:type="int" name="entity_id" identity="true" unsigned="true" nullable="false" comment="Entity Id" />
      <column xsi:type="varchar" length="100" name="identifier" nullable="false" comment="Identifier" />
      <column xsi:type="timestamp" name="created_at" nullable="false" comment="Creation Time" default="CURRENT_TIMESTAMP" on_update="false" />
      <column xsi:type="timestamp" name="updated_at" nullable="false" comment="Update Time" default="CURRENT_TIMESTAMP" on_update="true" />
      <!-- // Add more static attributes here...-->
      <constraint xsi:type="primary" referenceId="PRIMARY">
          <column name="entity_id" />
      </constraint>
      <index referenceId="IDENTIFIER" indexType="btree" disabled="true">
          <column name="identifier" />
      </index>
       <constraint xsi:type="unique" referenceId="UNIQUE">
          <column name="identifier" />
       </constraint>
  </table>

Or another solution is that you can remove index row and add UNIQUE constraint row So index will be removed and UNIQUE constraint will be added.

Remove below code:

<index referenceId="IDENTIFIER" indexType="btree">
            <column name="identifier" />
</index>

Add below code:

<constraint xsi:type="unique" referenceId="UNIQUE">
           <column name="identifier" />
 </constraint>

madhurajawat on Apr 27, 2020