Make column unique in install schema script

by

The question:

I want my column to be unique. It is not an EAV attribute. I have seen some tutorials, where indexes are unique, but I am not sure it is not the same, as making column unique (or am I wrong?). Is there any way to achieve this?

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

You can use addIndex method having type MagentoFrameworkDBAdapterAdapterInterface::INDEX_TYPE_UNIQUE, like:

$table = $installer->getConnection()->newTable(
        $installer->getTable(Helper::CONNECTOR_CHANNELS_STANDARD_TABLE)
    )->addColumn(
        'entity_id',
        Table::TYPE_INTEGER,
        null,
        ['identity' => true, 'unsigned' => true, 'nullable' => false, 'primary' => true],
        'Id'
    )->addColumn(
        'name',
        Table::TYPE_TEXT,
        255,
        [],
        'Channel Name'
    )->addIndex(
        $installer->getIdxName(
            Helper::CONNECTOR_CHANNELS_STANDARD_TABLE,
            ['name'],
            MagentoFrameworkDBAdapterAdapterInterface::INDEX_TYPE_UNIQUE
        ),
        'name',
        ['type' => MagentoFrameworkDBAdapterAdapterInterface::INDEX_TYPE_UNIQUE]
    )->setComment(
        'Connector Channels'
    );
    $installer->getConnection()->createTable($table);

Helper::CONNECTOR_CHANNELS_STANDARD_TABLE is table name (string).

Update:

If you adding the index manually by a table name you should pass the Index Type as a string (fourth argument):

    $setup->getConnection()->addIndex(
        $setup->getTable('my_table'),
        $setup->getIdxName(
            Helper::CONNECTOR_PRIVATE_DATA_TABLE,
            ['protect_code'],
            AdapterInterface::INDEX_TYPE_UNIQUE
        ),
        'protect_code',
        AdapterInterface::INDEX_TYPE_UNIQUE
    );

For more info see:

MagentoFrameworkDBAdapterPdoMysql::addIndex(
    $tableName,
    $indexName,
    $fields,
    $indexType = AdapterInterface::INDEX_TYPE_INDEX,
    $schemaName = null
)

and

MagentoFrameworkDBDdlTable::addIndex($indexName, $fields, $options = [])

Method 2

Ok, I haven’t found any way to do it in ‘magento way’, so ugly solution is just: $setup->getConnection()->query("ALTER TABLE " . $tableName . " ADD UNIQUE (" . $uniqueColumnName . ");");


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment