4

I'm trying to build a custom module in Magento 2 v0.74.0-beta1.
This module should contain 2 simple entities, each one with its table and a many to many relation between them through a third table.
Nothing fancy..see the image below.

db schema

When running the install script (php setup/index.php update via cli) the main tables are created but i get an error when creating the link table Error Code : 1215 Cannot add foreign key constraint.

Here is my code from app/code/Testing/Demo/Setup/InstallSchema.php (Testing - vendor name, Demo - module name):

<?php

namespace Testing\Demo\Setup;

use Magento\Framework\DB\Adapter\AdapterInterface;
use Magento\Framework\DB\Ddl\Table;
use Magento\Framework\Setup\InstallSchemaInterface;
use Magento\Framework\Setup\ModuleContextInterface;
use Magento\Framework\Setup\SchemaSetupInterface;

class InstallSchema implements InstallSchemaInterface
{
    /**
     * install tables
     *
     * @param SchemaSetupInterface $setup
     * @param ModuleContextInterface $context
     * @return void
     * @SuppressWarnings(PHPMD.ExcessiveMethodLength)
     */
    public function install(SchemaSetupInterface $setup, ModuleContextInterface $context)
    {
        $installer = $setup;
        $installer->startSetup();
        if (!$installer->tableExists('testing_demo_author')) {
            $table = $installer->getConnection()->newTable(
                $installer->getTable('testing_demo_author')
            )
            ->addColumn(
                'author_id',
                Table::TYPE_INTEGER,
                null,
                [
                    'identity' => true,
                    'nullable' => false,
                    'primary' => true
                ],
                'Author ID'
            )
            ->addColumn(
                'name',
                Table::TYPE_TEXT,
                255,
                ['nullable => false'],
                'Author Name'
            )
            ->addColumn(
                'created_at',
                Table::TYPE_TIMESTAMP,
                null,
                [],
                'Author Created At'
            )
            ->addColumn(
                'updated_at',
                Table::TYPE_TIMESTAMP,
                null,
                [],
                'Author Updated At'
            )
            ->setComment('Author Table');
            $installer->getConnection()->createTable($table);
        }
        if (!$installer->tableExists('testing_demo_article')) {
            $table = $installer->getConnection()->newTable(
                $installer->getTable('testing_demo_article')
            )
            ->addColumn(
                'article_id',
                Table::TYPE_INTEGER,
                null,
                [
                    'identity' => true,
                    'nullable' => false,
                    'primary' => true
                ],
                'Article ID'
            )
            ->addColumn(
                'title',
                Table::TYPE_TEXT,
                255,
                ['nullable => false'],
                'Article Title'
            )
            ->addColumn(
                'description',
                Table::TYPE_TEXT,
                '64k',
                ['nullable => false'],
                'Article Description'
            )

            ->addColumn(
                'created_at',
                Table::TYPE_TIMESTAMP,
                null,
                [],
                'Article Created At'
            )
            ->addColumn(
                'updated_at',
                Table::TYPE_TIMESTAMP,
                null,
                [],
                'Article Updated At'
            )
            ->setComment('Article Table');
            $installer->getConnection()->createTable($table);
        }

        if (!$installer->tableExists('testing_demo_author_article')) {
            $table = $installer->getConnection()
                ->newTable($installer->getTable('testing_demo_author_article'));
            $table->addColumn(
                    'author_id',
                    Table::TYPE_INTEGER,
                    null,
                    [
                        'unsigned' => true,
                        'nullable' => false,
                        'primary'   => true,
                    ],
                    'Author ID'
                )
                ->addColumn(
                    'article_id',
                    Table::TYPE_INTEGER,
                    null,
                    [
                        'unsigned' => true,
                        'nullable' => false,
                        'primary'   => true,
                    ],
                    'Article ID'
                )
                ->addColumn(
                    'position',
                    Table::TYPE_INTEGER,
                    null,
                    [
                        'nullable' => false,
                        'default' => '0'
                    ],
                    'Position'
                )
                ->addIndex(
                    $installer->getIdxName('testing_demo_author_article', ['author_id']),
                    ['author_id']
                )
                ->addIndex(
                    $installer->getIdxName('testing_demo_author_article', ['article_id']),
                    ['article_id']
                )
                ->addForeignKey(
                    $installer->getFkName(
                        'testing_demo_author_article',
                        'author_id',
                        'testing_demo_author',
                        'author_id'
                    ),
                    'author_id',
                    $installer->getTable('testing_demo_author'),
                    'author_id',
                    Table::ACTION_CASCADE,
                    Table::ACTION_CASCADE
                )
                ->addForeignKey(
                    $installer->getFkName(
                        'testing_demo_author_article',
                        'article_id',
                        'testing_demo_article',
                        'article_id'
                    ),
                    'article_id',
                    $installer->getTable('testing_demo_article'),
                    'article_id',
                    Table::ACTION_CASCADE,
                    Table::ACTION_CASCADE
                )
                ->addIndex(
                    $installer->getIdxName(
                        'testing_demo_author_article',
                        [
                            'author_id',
                            'article_id'
                        ],
                        AdapterInterface::INDEX_TYPE_UNIQUE
                    ),
                    [
                        'author_id',
                        'article_id'
                    ],
                    [
                        'type' => AdapterInterface::INDEX_TYPE_UNIQUE
                    ]
                )
                ->setComment('Author To Article Link Table');
            $installer->getConnection()->createTable($table);
        }
        $installer->endSetup();
    }
}

The generated scripts that are executed are:

CREATE TABLE `testing_demo_author` (
  `author_id` int NOT NULL auto_increment COMMENT 'Author ID' ,
  `name` varchar(255) NULL COMMENT 'Author Name' ,
  `created_at` timestamp NULL default NULL COMMENT 'Author Created At' ,
  `updated_at` timestamp NULL default NULL COMMENT 'Author Updated At' ,
  PRIMARY KEY (`author_id`),
  INDEX `IDX_TESTING_DEMO_AUTHOR_ID` (`author_id`)
) COMMENT='Author Table' ENGINE=INNODB charset=utf8 COLLATE=utf8_general_ci;

CREATE TABLE `testing_demo_article` (
  `article_id` int NOT NULL auto_increment COMMENT 'Article ID' ,
  `title` varchar(255) NULL COMMENT 'Article Title' ,
  `description` text NULL COMMENT 'Article Description' ,
  `created_at` timestamp NULL default NULL COMMENT 'Article Created At' ,
  `updated_at` timestamp NULL default NULL COMMENT 'Article Updated At' ,
  PRIMARY KEY (`article_id`),
  INDEX `IDX_TESTING_DEMO_ARTICLE_ID` (`article_id`)
) COMMENT='Article Table' ENGINE=INNODB charset=utf8 COLLATE=utf8_general_ci;

CREATE TABLE `testing_demo_author_article` (
  `author_id` int UNSIGNED NOT NULL COMMENT 'Author ID' ,
  `article_id` int UNSIGNED NOT NULL COMMENT 'Article ID' ,
  `position` int NOT NULL default '0' COMMENT 'Position' ,
  PRIMARY KEY (`author_id`, `article_id`),
  INDEX `IDX_TESTING_DEMO_AUTHOR_ARTICLE_AUTHOR_ID` (`author_id`),
  INDEX `IDX_TESTING_DEMO_AUTHOR_ARTICLE_ARTICLE_ID` (`article_id`),
  UNIQUE `UNQ_TESTING_DEMO_AUTHOR_ARTICLE_AUTHOR_ID_ARTICLE_ID` (`author_id`, `article_id`),
  CONSTRAINT `FK_28E3EEEF7CD69E4787AE4826A331D49F` FOREIGN KEY (`author_id`) REFERENCES `testing_demo_author` (`author_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_247EC1688F0CAA785ECB3A4BA5398032` FOREIGN KEY (`article_id`) REFERENCES `testing_demo_article` (`article_id`) ON DELETE CASCADE ON UPDATE CASCADE
) COMMENT='Author To Article Link Table' ENGINE=INNODB charset=utf8 COLLATE=utf8_general_ci;

What am I doing wrong?
I know what the error means, but I cannot understand why can't the FK be created.

5

Usually foreign key should be created on the column with exactly the same type and configuration as main table column.

In your table testing_demo_author

author_id int NOT NULL auto_increment

In your dependent table testing_demo_author_article

author_id int UNSIGNED NOT NULL COMMENT

If you take a look type is different int vs int UNSIGNED.

With article_id column you have the same problem.

Try to use the same create column statement.

  • Ok. +1 for spotting this. but unfortunately nothing changes. I removed the UNSIGNED from the link table, but I still get the same error. – Marius Mar 30 '15 at 16:11
  • NO wait. You are right. I modified the wrong file. Thanks you. – Marius Mar 30 '15 at 16:19
5

I'm not into Magento 2 development yet but according to the error message it seems MySQL specific and not Magento related.

One issue I spotted is the data definition: While article_id and author_id are of types int in their tables, the columns are of type int UNSIGNED in the link table. This could cause an issue during foreign key creation.

Sometimes it's about the small things that matter.

  • I agree this is an sql issue, but my problem is that I don't write the sql queries directly. Magento does. I just logged them. The question is how o configure the install script to make it work. Also, as explained for the other answer, removing UNSIGNED still produces the same error. – Marius Mar 30 '15 at 16:13
  • Sorry, didn't see the other answer popping up on the mobile. I'll have a look again. – Anna Völkl Mar 30 '15 at 16:20
  • Apparently I need a break. I was editing the wrong file. You are right. removing unsigned solves it. But the other answer came about 4 minutes earlier so I feel obligated to accept that one. – Marius Mar 30 '15 at 16:20
  • Sure! I'm happy the answer is right :-) – Anna Völkl Mar 30 '15 at 16:21
  • BTW: I'd rather go with int UNSIGNED for both cases than without. Makes sense for the keys :) – Anna Völkl Mar 30 '15 at 16:23
1
<?php
namespace frontname\modulename\Setup;

use Magento\Framework\Setup\InstallSchemaInterface;
use Magento\Framework\Setup\ModuleContextInterface;
use Magento\Framework\Setup\SchemaSetupInterface;

class InstallSchema implements InstallSchemaInterface
{

    public function install(SchemaSetupInterface $setup, ModuleContextInterface $context)
    {
        $installer = $setup;

        $installer->startSetup();


        $table = $installer->getConnection()->newTable(
            $installer->getTable('autocrosssell')
        )->addColumn(
            'entity_id',
            \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
            null,
            ['unsigned' => true, 'nullable' => false, 'primary' => true,],
            'Entity Id'
        ) ->addColumn(
            'product_id',
            \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
            null,
            ['unsigned' => true, 'nullable' => false, 'default' => '0'],
            'Product ID'
        ) ->addColumn(
            'store_id',
            \Magento\Framework\DB\Ddl\Table::TYPE_SMALLINT,
            null,
            ['unsigned' => true, 'nullable' => false, 'default' => '0'],
            'Store Id'
        )->addColumn(
            'related_array',
            \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
            100,
            ['nullable' => false],
            'Name'
        )->addForeignKey(
            $installer->getFkName(
                'autocrosssell',
                'store_id',
                'core_store',
                'store_id'
            ),
            'store_id',
            $installer->getTable('core_store'),
            'store_id',
            \Magento\Framework\DB\Ddl\Table::ACTION_CASCADE
        )->setComment(
            'AutoCrosssell Table'
        );

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

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.