3

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?

2 Answers 2

6

You can use addIndex method having type \Magento\Framework\DB\Adapter\AdapterInterface::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'],
            \Magento\Framework\DB\Adapter\AdapterInterface::INDEX_TYPE_UNIQUE
        ),
        'name',
        ['type' => \Magento\Framework\DB\Adapter\AdapterInterface::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:

\Magento\Framework\DB\Adapter\Pdo\Mysql::addIndex(
    $tableName,
    $indexName,
    $fields,
    $indexType = AdapterInterface::INDEX_TYPE_INDEX,
    $schemaName = null
)

and

\Magento\Framework\DB\Ddl\Table::addIndex($indexName, $fields, $options = [])
2
  • I got an error ` Warning: strtolower() expects parameter 1 to be string, array given in D:\OpenServer\domains\midwest.loc\vendor\magento\framewor k\DB\Adapter\Pdo\Mysql.php on line 2563`. The last parameter of addIndex method should be just \Magento\Framework\DB\Adapter\AdapterInterface::INDEX_TYPE_UNIQUE, without array Commented Sep 19, 2017 at 7:15
  • @KateSuykovskaya Thanks for discovering the error, I have changed the code Commented Sep 19, 2017 at 7:34
1

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 . ");");

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.