in magento2 add unique key constaint on column which has already foreign key adding for vendor code

Question

i trying to add unique key constraint to company_id column in company_credit table but i facing issue like duplicate key Syntax error or access violation: 1061 Duplicate key name 'COMPANY_CREDIT_COMPANY_ID', query was: ALTER TABLE company_credit ADD CONSTRAINT COMPANY_CREDIT_COMPANY_ID UNIQUE KEY (company_id), COMMENT='Company Credit'

so basically this is my code in dbschema.xml

<table name="company_credit" resource="default" engine="innodb" comment="Company Credit">
<!-- Other table definitions here -->
    <constraint xsi:type="unique" referenceId="COMPANY_CREDIT_UNIQUE_COMPANY_ID">
        <column name="company_id"/>
    </constraint>
</table>

so using this code in db_scheme_whitelist.json this code is coming

"company_credit": {
    "constraint": {
        "COMPANY_CREDIT_COMPANY_ID": true
    }
}

and this is vendor dbschema.xml code which is causing issue

 <constraint xsi:type="foreign" referenceId="COMPANY_CREDIT_COMPANY_ID_COMPANY_ENTITY_ID"
                table="company_credit" column="company_id" referenceTable="company" referenceColumn="entity_id"
                onDelete="CASCADE"/>
    <index referenceId="COMPANY_CREDIT_COMPANY_ID" indexType="btree">
        <column name="company_id"/>
    </index>

so now when i running upgrade command error is coming

db screenshot

Answer 1

In order to ensure unique values, an INDEX is created for the UNIQUE column. Your unique keys are essentially b-tree type indexes.

With declarative schema, you can either add INDEX or UNIQUE for a column because it reuses the same referenceId. The algorithm that creates the names does not create a unique name for both.

In the core file, an index is created with a name COMPANY_CREDIT_COMPANY_ID for column company_id.

<index referenceId="COMPANY_CREDIT_COMPANY_ID" indexType="btree">
    <column name="company_id"/>
</index>

While adding an UNIQUE constraint on company_id, it tries to create an index with the same name COMPANY_CREDIT_COMPANY_ID.

For adding UNIQUE constraint to company_id, you need to first drop the existing index COMPANY_CREDIT_COMPANY_ID and then add a UNIQUE constraint to that column.

You can achieve this using a schema patch as below

<?php

namespace Vendor\Module\Setup\Patch\Schema;

use Magento\Framework\DB\Adapter\AdapterInterface;
use Magento\Framework\Setup\Patch\SchemaPatchInterface;
use Magento\Framework\Setup\SchemaSetupInterface;

class AddUniqueConstraintToCompanyId implements SchemaPatchInterface
{
    /**
     * @var SchemaSetupInterface
     */
    private $setup;

    /**
     * @param SchemaSetupInterface $setup
     */
    public function __construct(
        SchemaSetupInterface $setup
    ) {
        $this->setup = $setup;
    }

    /**
     * @inheritDoc
     */
    public function apply()
    {
        $this->setup->startSetup();
        
        $this->setup->getConnection()->dropIndex(
            $this->setup->getTable('company_credit'),
            'COMPANY_CREDIT_COMPANY_ID'
        );

        $this->setup->getConnection()->addIndex(
            $this->setup->getTable('company_credit'),
            $this->setup->getIdxName(
                'company_credit',
                ['company_id'],
                AdapterInterface::INDEX_TYPE_UNIQUE
            ),
            ['company_id'],
            AdapterInterface::INDEX_TYPE_UNIQUE
        );

        $this->setup->endSetup();
        return $this;
    }

    /**
     * @inheritDoc
     */
    public static function getDependencies()
    {
        return [];
    }

    /**
     * @inheritDoc
     */
    public function getAliases()
    {
        return [];
    }
}