Magento 2 adding constraints in the install script

Question

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.

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.

Answer 1

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.

Answer 2

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.

Answer 3

<?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();
    }
}