3

I know the syntax to create table via InstallSchema in Magento 2, but I don't know how to add a UNIQUE Constraint on two columns of my table.

Here is the syntax of my table -

$table_mediatype = $setup->getConnection()->newTable($setup->getTable('mediatype'));
$table_mediatype->addColumn(
    'id',
    \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
    null,
    array('identity' => true,'nullable' => false,'primary' => true),
    'Entity ID'
);
$table_mediatype->addColumn(
    'mediadownloader_id',
    \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
    null,
    array('nullable' => false),
    'Mediadownloader ID'
);
$table_mediatype->addColumn(
    'product_id',
    \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
    null,
    array('nullable' => false),
    'Product ID'
);
$table_mediatype->addColumn(
    'media_type',
    \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
    25,
    array('nullable' => false),
    'Associated Media Type'
);

Via an SQL query we can add a UNIQUE Constraint like this -

ALTER TABLE netzwelt_mediatype
ADD CONSTRAINT unique_product_id_with_media_type UNIQUE unique_product_id_with_media_type(
    `product_id` ,
    `media_type`
);

But I don't know how to add it via 'InstallSchema'. Can anyone help me in this?

0

1 Answer 1

6

To create UNIQUE Constraint you can use

Magento\Framework\DB\Adapter\AdapterInterface::INDEX_TYPE_UNIQUE

For example,

      $table = $installer->getConnection()->newTable(
            $installer->getTable('mediatype')
        )->addColumn(
            'key',
            \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
            32,
            ['nullable' => false],
            'Key code'
        )->addIndex(
            $installer->getIdxName(
                $installer->getTable('mediatype'),
                ['key'],
                \Magento\Framework\DB\Adapter\AdapterInterface::INDEX_TYPE_UNIQUE
            ),
            ['key'],
            ['type' => \Magento\Framework\DB\Adapter\AdapterInterface::INDEX_TYPE_UNIQUE]
        );
        $installer->getConnection()->createTable($table);

EDIT

When you a look into

 public function addIndex($indexName, $fields, $options = []) 

from

Magento\Framework\DB\Ddl\Table.php

First, paramater is $indexName name which means the same you writes as unique_product_id_with_media_type (in core php) in my sample code I added

$installer->getIdxName(
                $installer->getTable('oauth_consumer'),
                ['key'],
                \Magento\Framework\DB\Adapter\AdapterInterface::INDEX_TYPE_UNIQUE
            )

because It creates the same key(here MEDIATYPE_KEY where mediatype is table name and key is column name ) which you want based on the current table name and column name. (If you want you can pass unique_product_id_with_media_type here but it's not good practice )

the second parameter is $fields means the columns which you want to make unique you can pass the only column as a string or multiple columns as an array

and the last third paramater is $options from here you can tell which option you want (like UNIQUE or FK)

here on third option I passed as

Magento\Framework\DB\Adapter\AdapterInterface::INDEX_TYPE_UNIQUE to create unique key

7
  • Is this code allow to alter one column to UNIQUE or it perform like my query doing .(See at the end of my code )....
    – Atul
    Commented Dec 22, 2017 at 11:54
  • It will add UNIQUE Constraint so your column has unique value same like product SKU @Atul
    – Keyur Shah
    Commented Dec 22, 2017 at 11:58
  • As I need to make "product_id & media_type" both column sould have unique values as combination ....SO how can I write this....Because in your ans I didn't see where to write constraint-name like 'unique_product_id_with_media_type' and Unique index-name 'unique_product_id_with_media_type' as we write in sql query ...
    – Atul
    Commented Dec 22, 2017 at 12:07
  • Please see my edit @Atul
    – Keyur Shah
    Commented Dec 22, 2017 at 12:19
  • thx for clarification....let me try via this ..........
    – Atul
    Commented Dec 22, 2017 at 12:40

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