Magento 2 Installation – Adding Constraints in the Install Script

installationmagento2-dev-betasql

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.

Best Answer

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.

Related Topic