Can't create unique and fulltext index on the same column

Question

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

Answer 1

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.