How we can add unique containt for column in magento 2?

Question

I am having custom table called channel with channel_id,channel_code,channel_name and so on. I want to make channel_code and channel_name unique by using installscript or upgradescript here is my code:

<?php

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of InstallSchema
 *
 * @author Pramod Kharade
 */
namespace Blazeclan\Channels\Setup;
use Magento\Framework\Setup\InstallSchemaInterface;
use Magento\Framework\Setup\ModuleContextInterface;
use Magento\Framework\Setup\SchemaSetupInterface;
use Magento\Framework\DB\Ddl\Table;

class InstallSchema implements InstallSchemaInterface {
    //put your code here
    public function install(SchemaSetupInterface $setup, ModuleContextInterface $context) {
        $installer = $setup;
        $installer->startSetup();

        // Get tutorial_simplenews table
        $tableName = $installer->getTable('blazeclan_channels');
        // Check if the table already exists
        if ($installer->getConnection()->isTableExists($tableName) != true) {
            // Create tutorial_simplenews table
            $table = $installer->getConnection()
                ->newTable($tableName)
                 ->addColumn(
                    'channel_id',
                    Table::TYPE_INTEGER,
                    null,
                    [
                        'identity' => true,
                        'unsigned' => true,
                        'nullable' => false,
                        'primary' => true
                    ],
                    'Channel ID'
                )
                ->addColumn(
                    'channel_code',
                    Table::TYPE_TEXT,
                    null,
                    ['nullable' => false,'DEFAULT' => null],
                    'Channel Code'
                )
                ->addColumn(
                    'channel_name',
                    Table::TYPE_TEXT,
                    null,
                    ['nullable' => false,'DEFAULT' => null],
                    'Channel Name'
                )
                ->addColumn(
                    'channel_image',
                    \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
                    255,
                    [],
                    'Channel Image'
                )
                 ->addColumn(
                    'channel_createdby',
                    Table::TYPE_TEXT,
                    null,
                    ['nullable' => false, 'default' => ''],
                    'Channel Created By'
                )
                ->addColumn(
                    'channel_modifiedby',
                    Table::TYPE_TEXT,
                    null,
                    ['nullable' => false, 'default' => ''],
                    'Channel Modified  By'
                )
                ->addColumn(
                    'created_at',
                    Table::TYPE_DATETIME,
                    null,
                    ['nullable' => false],
                    'Created At'
                )
                    ->addColumn(
                    'updated_at',
                    Table::TYPE_DATETIME,
                    null,
                    ['nullable' => false,'default' => Table::TIMESTAMP_INIT_UPDATE],
                    'updated_at At'
                )
                ->addColumn(
                    'status',
                    Table::TYPE_SMALLINT,
                    null,
                    ['nullable' => false, 'default' => '0'],
                    'Status'
                )

                ->setComment('channels Table')
                ->setOption('type', 'InnoDB')
                ->setOption('charset', 'utf8');
            $installer->getConnection()->createTable($table);
        }


        $installer->endSetup();
    }

}

Thanks In advance!

Answer 1

You can just use unique index like in this example:

$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'
    )->addColumn(
        'class',
        Table::TYPE_TEXT,
        '64k',
        [],
        'Class'
    )->addColumn(
        'user_id',
        Table::TYPE_TEXT,
        255,
        [],
        'User Id'
    )->addIndex(
        $installer->getIdxName(
            Helper::CONNECTOR_CHANNELS_STANDARD_TABLE,
            ['name'],
            AdapterInterface::INDEX_TYPE_UNIQUE
        ),
        'name',
        ['type' => AdapterInterface::INDEX_TYPE_UNIQUE]
    )->setComment(
        'Connector Channels'
    );
    $installer->getConnection()->createTable($table);

Where the Helper::CONNECTOR_CHANNELS_STANDARD_TABLE is your table name, the AdapterInterface is Magento\Framework\DB\Adapter\AdapterInterface

So in your install script it should looks like this:

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

In the upgrade script you can use this example from the catalog module:

    $setup->getConnection()->addIndex(
        $setup->getTable('catalog_category_product'),
        $setup->getIdxName(
            'catalog_category_product',
            ['category_id', 'product_id'],
            \Magento\Framework\DB\Adapter\AdapterInterface::INDEX_TYPE_UNIQUE
        ),
        ['category_id', 'product_id'],
        \Magento\Framework\DB\Adapter\AdapterInterface::INDEX_TYPE_UNIQUE
    );

So in your case it should looks like this:

    $tableName = $installer->getTable('blazeclan_channels');
    $setup->getConnection()->addIndex(
        $tableName,
        $setup->getIdxName(
            $tableName,
            ['channel_code', 'channel_name'],
            \Magento\Framework\DB\Adapter\AdapterInterface::INDEX_TYPE_UNIQUE
        ),
        ['channel_code', 'channel_name'],
        \Magento\Framework\DB\Adapter\AdapterInterface::INDEX_TYPE_UNIQUE
    );

More info you can find in the \Magento\Framework\DB\Adapter\Pdo\Mysql class.