0

I'm creating table with reference key of catalog_product_entity, here I'm little bit of confusing while converting Magento 1.0 to Magento 2.0.

Can you clarify it.

In magento 1.0 simply I can run the below script it will execute and create the table right?

<?php

$installer = $this;
/* @var $installer Mage_Core_Model_Resource_Setup */

$installer->startSetup();

$installer->run("

-- DROP TABLE IF EXISTS `{$this->getTable('custom_info')}`;
CREATE TABLE `{$this->getTable('custom_info')}` (
  `id` INTEGER unsigned NOT NULL auto_increment,
  `product_id` INTEGER unsigned NOT NULL,
  `email` TEXT NOT NULL default '',
  `is_active` ENUM('0','1') NOT NULL DEFAULT '0',
  `date` DATETIME default '0000-00-00 00:00:00',  
  PRIMARY KEY  (`id`),
  KEY `FK_custom_PRODUCT_ID` (`product_id`),
  CONSTRAINT `FK_custom_PRODUCT_ID` FOREIGN KEY (`product_id`) REFERENCES `{$this->getTable('catalog_product_entity')}` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

");

$installer->endSetup();

In Magento 2.0 I have written code as below is it correct procedure or else wrong?

<?php

namespace Learning\Custom\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
{

    /**
 * Installs DB schema for a module
 *
 * @param SchemaSetupInterface $setup
 * @param ModuleContextInterface $context
 * @return void
 */
public function install(SchemaSetupInterface $setup, ModuleContextInterface $context)
{
    $installer = $setup;
    $installer->startSetup();

    $table = $installer->getConnection()->newTable($installer->getTable('outofstocksubscription_info'))
        ->addColumn(
            'id',
            Table::TYPE_INTEGER,
            10,
            ['identity' => true, 'nullable' => false, 'primary' => true],
            'Post ID'
        )
        ->addColumn(
            'product_id',
            Table::TYPE_INTEGER,
            10,
            ['unsigned' => true, 'nullable' => false, 'default' => '0'],
            'Product Id'
        )
        ->addColumn(
            'email',
            Table::TYPE_TEXT,
            null,
            ['nullable' => false,],
            'E Mail'
        )
        ->addColumn(
            'is_active',
            Table::TYPE_BOOLEAN,
            null,
            ['nullable' => false, 'default' => '0'],
            'Is Active'
        )
        ->addForeignKey(
            $installer->getFkName('outofstocksubscription_info', 'product_id', 'catalog_product_entity', 'entity_id'),
            'product_id',
            $installer->getTable('catalog_product_entity'),
            'entity_id',
            Table::ACTION_CASCADE
        )
        ->addColumn(
            'date',
            Table::TYPE_DATE,
            null,
            [],
            'Date'
        )
        ->setComment(
            'Out of Stock Subscription'
        );

    $installer->getConnection()->createTable($table);
    $installer->endSetup();
}

}

If it is wrong please correct me.

When I ran command: php bin/magento setup:upgrade error is came.

Error

SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint, query was: CREATE TABLE IF NOT EXISTS custom_info ( id smallint NOT NULL auto_increment COMMENT 'Post ID' , product_id smallint UNSIGNED NOT NULL COMMENT 'Product Id' , email text NOT NULL COMMENT 'E Mail' , is_active bool NOT NULL default '0' COMMENT 'Is Active' , date date NULL COMMENT 'Date' , PRIMARY KEY (id), CONSTRAINT CUSTOM__INFO_ID_CATALOG_PRODUCT_ENTITY_ENTITY_ID FOREIGN KEY (id) REFERENCES catalog_product_entity (entity_id) ON DELETE CASCADE ) COMMENT='Custom Table' ENGINE=INNODB charset=utf8 COLLATE=utf8_general_ci

1

I think you need remove params 6 in addForeignKey function because in:

/**
 * Add Foreign Key to table
 *
 * @param string $fkName        the foreign key name
 * @param string $column        the foreign key column name
 * @param string $refTable      the reference table name
 * @param string $refColumn     the reference table column name
 * @param string $onDelete      the action on delete row
 * @return $this
 * @throws \Zend_Db_Exception
 * @SuppressWarnings(PHPMD.CyclomaticComplexity)
 */
public function addForeignKey($fkName, $column, $refTable, $refColumn, $onDelete = null)
{
    $upperName = strtoupper($fkName);

    // validate column name
    if (!isset($this->_columns[strtoupper($column)])) {
        throw new \Zend_Db_Exception('Undefined column "' . $column . '"');
    }

    switch ($onDelete) {
        case self::ACTION_CASCADE:
        case self::ACTION_RESTRICT:
        case self::ACTION_SET_DEFAULT:
        case self::ACTION_SET_NULL:
            break;
        default:
            $onDelete = self::ACTION_NO_ACTION;
    }

    $this->_foreignKeys[$upperName] = [
        'FK_NAME' => $fkName,
        'COLUMN_NAME' => $column,
        'REF_TABLE_NAME' => $refTable,
        'REF_COLUMN_NAME' => $refColumn,
        'ON_DELETE' => $onDelete
    ];

    return $this;
}
  • When I ran my script Error is trowing. see my thread I have updated. – M_N Nov 30 '15 at 9:09
  • type of entity_id in catalog_product_entity as int(10) but id column in custom_info have type smallint(6). You should change and try again – Thao Pham Nov 30 '15 at 9:23
  • I have modified as you instructed, now no error is came. but when I ran website the script is not executed (table not created). – M_N Nov 30 '15 at 10:16
  • you access setup_module table, find and delete record have module column as Learning_Custom (name of your module) , after run bin/magento setup:upgrade – Thao Pham Nov 30 '15 at 10:46
  • I think setup_module (Magento 2.0) is equal to core_resource (Magento 1.0) right? – M_N Nov 30 '15 at 11:31
0

For me it looks ok. The main difference with upgrade or install script in magento2 is, that you dont have several scripts. Magento2 just have one script, running multiple times, so you have to handle your versioning by yourself.

  • When I ran my script Error is trowing. see my thread I have updated. – M_N Nov 30 '15 at 9:17

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.