Magento2 Adding foreign key constraint to a database table

Adding a foreign key constraint to a database table in Magento2 can be a bit confusing. Here’s a simple example of how to create a table with a foreign key to entity_id in the customer_entity table and entity_id in the catalog_product_entity table. These is also an example here of creating an index for your table.

<?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;
use Magento\Framework\DB\Adapter\AdapterInterface;

/**
 * Class UpgradeSchema
 * @package Vendor\ModuleName\Setup
 */
class UpgradeSchema implements UpgradeSchemaInterface
{
	public function upgrade(SchemaSetupInterface $setup, ModuleContextInterface $context)
    {
        $installer = $setup->startSetup();

        if (version_compare($context->getVersion(), '0.0.2') < 0) {
        	$this->createTableWithForeignKeys($installer);
        }
    }

    /**
     * Create tables for keeping track of many of each product a customer has on backorder
     * @param SchemaSetupInterface $installer
     */
    protected function createTableWithForeignKeys(SchemaSetupInterface $installer)
    {
    	$table = $installer->getConnection()
            ->newTable('my_foreign_key_table')
            ->addColumn(
                'entity_id',
                Table::TYPE_INTEGER,
                null,
                ['identity' => true, 'unsigned' => true, 'nullable' => false, 'primary' => true],
                'Backorder Unique entity ID'
            )
            ->addColumn(
                'product_id',
                Table::TYPE_INTEGER,
                null,
                ['unsigned'=>true, 'nullable'=>false, 'default' => '0'],
                'Product Id'
            )
            ->addColumn(
                'customer_id',
                Table::TYPE_INTEGER,
                null,
                ['unsigned'=>true, 'nullable'=>false, 'default' => '0'],
                'Customer Id'
            )
            ->addIndex(
                $installer->getIdxName(
                    'my_foreign_key_table',
                    ['customer_id','product_id'],
                    AdapterInterface::INDEX_TYPE_UNIQUE
                ),
                ['customer_id','product_id'],
                AdapterInterface::INDEX_TYPE_UNIQUE
            )
            ->addForeignKey( // Add foreign key for table entity
                $installer->getFkName(
                    'my_foreign_key_table', // New table
                    'product_id', // Column in New Table
                    'catalog_product_entity', // Reference Table
                    'entity_id' // Column in Reference table
                ),
                'product_id', // New table column
                $installer->getTable('catalog_product_entity'), // Reference Table
                'entity_id', // Reference Table Column
                // When the parent is deleted, delete the row with foreign key
                Table::ACTION_CASCADE
            )
            ->addForeignKey( // Add foreign key for table entity
                $installer->getFkName(
                    'my_foreign_key_table', // New table
                    'customer_id', // Column in New Table
                    'customer_entity', // Reference Table
                    'entity_id' // Column in Reference table
                ),
                'customer_id', // New table column
                $installer->getTable('customer_entity'), // Reference Table
                'entity_id', // Reference Table Column
                // When the parent is deleted, delete the row with foreign key
                Table::ACTION_CASCADE
            )
            ->setComment('A comment about your table');

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

<?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;

use Magento\Framework\DB\Adapter\AdapterInterface;

/**

* Class UpgradeSchema

* @package Vendor\ModuleName\Setup

class UpgradeSchema implements UpgradeSchemaInterface

{

public function upgrade(SchemaSetupInterface $setup, ModuleContextInterface $context)

{

$installer = $setup->startSetup();

if (version_compare($context->getVersion(), '0.0.2') < 0) {

$this->createTableWithForeignKeys($installer);

}

/**

* Create tables for keeping track of many of each product a customer has on backorder

* @param SchemaSetupInterface $installer

protected function createTableWithForeignKeys(SchemaSetupInterface $installer)

{

$table = $installer->getConnection()

->newTable('my_foreign_key_table')

->addColumn(

'entity_id',

Table::TYPE_INTEGER,

null,

['identity' => true, 'unsigned' => true, 'nullable' => false, 'primary' => true],

'Backorder Unique entity ID'

)

->addColumn(

'product_id',

Table::TYPE_INTEGER,

null,

['unsigned'=>true, 'nullable'=>false, 'default' => '0'],

'Product Id'

)

->addColumn(

'customer_id',

Table::TYPE_INTEGER,

null,

['unsigned'=>true, 'nullable'=>false, 'default' => '0'],

'Customer Id'

)

->addIndex(

$installer->getIdxName(

'my_foreign_key_table',

['customer_id','product_id'],

AdapterInterface::INDEX_TYPE_UNIQUE

['customer_id','product_id'],

AdapterInterface::INDEX_TYPE_UNIQUE

)

->addForeignKey( // Add foreign key for table entity

$installer->getFkName(

'my_foreign_key_table', // New table

'product_id', // Column in New Table

'catalog_product_entity', // Reference Table

'entity_id' // Column in Reference table

'product_id', // New table column

$installer->getTable('catalog_product_entity'), // Reference Table

'entity_id', // Reference Table Column

// When the parent is deleted, delete the row with foreign key

Table::ACTION_CASCADE

)

->addForeignKey( // Add foreign key for table entity

$installer->getFkName(

'my_foreign_key_table', // New table

'customer_id', // Column in New Table

'customer_entity', // Reference Table

'entity_id' // Column in Reference table

'customer_id', // New table column

$installer->getTable('customer_entity'), // Reference Table

'entity_id', // Reference Table Column

// When the parent is deleted, delete the row with foreign key

Table::ACTION_CASCADE

)

->setComment('A comment about your table');

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

}