Magento 2 – Add website_id Column to Custom Table via Upgrade Schema

magento2upgradeschema

I want to add website_id column to my custom table with index and foreign key by UpgradeSchema.

It is easy to add by InstallSchema but I want to add website_id column by UpgradeSchema in my existing custom table.

Thanks

Best Answer

Try below code in your UpgradeSchema.php file :

<?php
/**
 * Copyright © 2015 Ihor Vansach (ihor@magefan.com). All rights reserved.
 * See LICENSE.txt for license details (http://opensource.org/licenses/osl-3.0.php).
 *
 */

namespace Vendor\ModuleName\Setup;

use Magento\Framework\Setup\UpgradeSchemaInterface;
use Magento\Framework\Setup\ModuleContextInterface;
use Magento\Framework\Setup\SchemaSetupInterface;
use Magento\Framework\DB\Ddl\Table;

/**
 * Custom Table update
 */
class UpgradeSchema implements UpgradeSchemaInterface
{
    /**
     * {@inheritdoc}
     * @SuppressWarnings(PHPMD.ExcessiveMethodLength)
     */
    public function upgrade(SchemaSetupInterface $setup, ModuleContextInterface $context)
    {
        $installer = $setup;
        $setup->startSetup();

        $version = $context->getVersion();
        $connection = $setup->getConnection();
        $tablename = "my_custom_table";
        if (version_compare($version, '1.0.6') < 0) 
        {
            //Custom table
           $connection->addColumn(
                $setup->getTable($tablename),
                'website_id',
                [
                    'type' => \Magento\Framework\DB\Ddl\Table::TYPE_SMALLINT,
                    'length' => 5,
                    'comment' =>'Website Id',
                    'unsigned'  => true,
                    'nullable' => false,
                ]

            );
            $connection->addIndex(
                $setup->getTable('my_custom_table'),
                $setup->getIdxName($setup->getTable('my_custom_table'),['website_id']),
                ['website_id']
            );
            $connection->addForeignKey(
                    $setup->getFkName($tablename,'website_id','store_website','website_id'),
                    $tablename,
                    'website_id',
                    $setup->getTable('store_website'),
                    'website_id',
                    Table::ACTION_CASCADE 
                );
        }

        $setup->endSetup();
    }
}

Note :

  • I have used my custom existing table called = my_custom_table which i have created using InstallSchema.php previously and now adding column website_id using UpgradeSchema.php
  • Linked with website_id primary key from store_website table.
  • Update your module version in module.xml and same version mention in UpgradeSchema.php file to compare.
  • Run php bin/magento setup:upgrade command again and check
Related Topic