1

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

6

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
  • I have added your code but addForeignKey logic wrong. My core store table data also deleted. – Suresh Chikani Jul 12 '17 at 5:59
  • @SHPatel ok got it , for that you can do one thing pass ACTION_NO_ACTION in Table parameter of Foreignkey so it will not delete the core store table data – Manthan Dave Jul 12 '17 at 6:06
  • I need to delete my website_id once website delete from store_website. – Suresh Chikani Jul 12 '17 at 6:12
  • @SHPatel ok got your point , and i have updated the answer please check. Actually need to put reverse logic in ForeignKey , First needs to mention custom table and second core table – Manthan Dave Jul 12 '17 at 6:59
  • Updated answer code is working. but before updated code was very struggle for me because of core admin store delete from core table and site working stop. By the way thanks for answer. – Suresh Chikani Jul 12 '17 at 11:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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