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

Improve this question

1 Answer 1

Reset to default
6

Try below code in your UpgradeSchema.php file :

<?php
/**
 * Copyright © 2015 Ihor Vansach ([email protected]). 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
Improve this answer
6
  • I have added your code but addForeignKey logic wrong. My core store table data also deleted.
    – Suresh Chikani
    Commented Jul 12, 2017 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
    Commented Jul 12, 2017 at 6:06
  • I need to delete my website_id once website delete from store_website.
    – Suresh Chikani
    Commented Jul 12, 2017 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
    Commented Jul 12, 2017 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
    Commented Jul 12, 2017 at 11:01

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.