0

My table in db_schema looks like below.

<table name="test">
    <column xsi:type="int" identity="true" name="test_id" padding="10" unsigned="true" nullable="false" comment="Entity Id"/>
    <column xsi:type="varchar" name="name" nullable="false" length="255" default="" />
    <constraint xsi:type="primary" referenceId="PRIMARY">
        <column name="test_id"/>
    </constraint>
    <index referenceId="TEST_NAME" indexType="fulltext">
        <column name="name"/>
    </index>
    <constraint xsi:type="unique" referenceId="TEST_NAME_UNIQUE">
        <column name="name"/>
    </constraint>
</table>

After setup:upgrade I get an error "SQLSTATE[42000]: Syntax error or access violation: 1061 Duplicate key name 'TEST_NAME', query was: CREATE TABLE test ( test_id int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT "Entity Id", name varchar(255) NOT NULL DEFAULT "" , CONSTRAINT PRIMARY KEY (test_id), CONSTRAINT TEST_NAME UNIQUE KEY (name), FULLTEXT INDEX TEST_NAME (name) ) ENGINE=innodb DEFAULT CHARSET=utf8 DEFAULT COLLATE=utf8_general_ci"

I know there cannot be two indexes with the same name, so how to customize the index name? I thought referenceId field indicates the name of index but I was wrong.

I'm using Magento 2.3.5 and MariaDB

2
  • Did you generate a db_schema_whitelist.json?
    – Daniel
    May 12 '20 at 10:50
  • @Daniel Yes, I did
    – zsDev
    May 12 '20 at 11:52
0

I came across the same issue when attempting to create an unique constraint and a btree index on the same field.

<constraint xsi:type="unique" referenceId="UNIQUE_PRODUCT_ATTRIBUTES_NAME">
    <column name="attribute_name"/>
</constraint>
<index referenceId="IDX_PRODUCT_ATTRIBUTES_NAME" indexType="btree">
    <column name="attribute_name"/>
</index>

and i was getting

SQLSTATE[42000]: Syntax error or access violation: 1061 Duplicate key name 'PRODUCT_ATTRIBUTES_ATTRIBUTE_NAME', query was: CREATE TABLE product_attributes ( entity_id int(11) UNSIGNED NOT NULL AUTO_INCREMENT, attribute_name varchar(64) NOT NULL, catalog_attribute_code varchar(64) NULL, CONSTRAINT PRIMARY KEY (entity_id), CONSTRAINT PRODUCT_ATTRIBUTES_ATTRIBUTE_NAME UNIQUE KEY (attribute_name), INDEX PRODUCT_ATTRIBUTES_ATTRIBUTE_NAME (attribute_name) ) ENGINE=innodb DEFAULT CHARSET=utf8 DEFAULT COLLATE=utf8_general_ci

Investigating some more, i read the following on this github post:

If you are facing this issue when creating both a unique constraint and index for specific column(s) you can remove the index from the schema. The unique constraint will already create an (unique) index on the column(s).

I personally never heard of UNIQUE contraints to create indexes, but investigating some more i came across this answer here:

A unique key is a special case of index, acting like a regular index with added checking for uniqueness. Using SHOW INDEXES FROM customer you can see your unique keys are in fact B-tree type indexes.

So, it looks like you cannot create another index on the unique field. As a workaround, i would suggest you add one more field to the FULLTEXT index so its name won't be the same as the unique field's automatically created index name.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.