5

I want to create custom filed say myname with constraints UNIQUE KEY in custom extension table.

In Magento1 we can define like this :

UNIQUE KEY `myname` (`myname`)

How can I do this in Magento2?

2 Answers 2

23

You can use Index Type as

Old Way: Using setup script

\Magento\Framework\DB\Adapter\AdapterInterface::INDEX_TYPE_UNIQUE
To add unique key use code,

->addIndex(
    $installer->getIdxName(
        'YOUR_TABLE_NAME',
        ['YOUR_UNIQUE_FIELD_NAME'],
        \Magento\Framework\DB\Adapter\AdapterInterface::INDEX_TYPE_UNIQUE
    ),
    ['YOUR_UNIQUE_FIELD_NAME'],
    ['type' => \Magento\Framework\DB\Adapter\AdapterInterface::INDEX_TYPE_UNIQUE]
)

You can add multiple column combination as UNIQUE with,

->addIndex(
    $installer->getIdxName(
        'catalog_product_entity_datetime',
        ['entity_id', 'attribute_id', 'store_id'],
        \Magento\Framework\DB\Adapter\AdapterInterface::INDEX_TYPE_UNIQUE
    ),
    ['entity_id', 'attribute_id', 'store_id'],
    ['type' => \Magento\Framework\DB\Adapter\AdapterInterface::INDEX_TYPE_UNIQUE]
)

Refer vendor\magento\module-catalog\Setup\InstallSchema.php

New Way:Using DB Schema

MyCompany/MyModule/etc/db_schema.xml :

<constraint xsi:type="unique" referenceId="DIRECTORY_COUNTRY_FORMAT_COUNTRY_ID_TYPE">
    <column name="country_id"/>
    <column name="type"/>
</constraint>

Full Snap code: https://github.com/magento/magento2/blob/2.4-develop/app/code/Magento/Directory/etc/db_schema.xml

2
2

In Magento 2.3, We can create unique key using declarative schema.

<?xml version="1.0"?>
<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table name="student" resource="default" engine="innodb" comment="Student Table">
        <column xsi:type="smallint" name="student_id" padding="6" unsigned="false" nullable="false" identity="true" comment="Student Id"/>
        <column xsi:type="varchar" name="roll_number" nullable="false" length="255" comment="Roll Number"/>
        <column xsi:type="varchar" name="student_name" nullable="true" comment="Student Name"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="student_id"/>
        </constraint>
        <constraint xsi:type="unique" referenceId="STUDENT_ROLL_NUMBER">
            <column name="roll_number"/>
        </constraint>
    </table>
</schema>

for more details refer https://devdocs.magento.com/guides/v2.4/extension-dev-guide/declarative-schema/db-schema.html

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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