Magento – Magento 2 : Adding a foreign key of customer_entity in a custom table

magento-2.1magento2

I'm trying to add a foreign key of the customer in my table in InstallSchema file :

$installer->getConnection()
                ->addColumn($installer->getTable('fmj_crf/projet'), 'customer_fk_id', Table::TYPE_INTEGER, null, array(
                    'unsigned' => true,
                    'nullable' => false,
                        ), 'customer Id');

        $installer->getConnection()->addForeignKey(
                $installer->getFkName('fmj_crf/projet', 'customer_fk_id', 'customer_entity', 'entity_id'), $installer->getTable('fmj_crf/projet'), 'customer_fk_id', $installer->getTable('customer_entity'), 'entity_id'
        );

but when I upgrade it gives me this error :

[PDOException]                                                                                             
  SQLSTATE[HY000]: General error: 1825 Failed to add the foreign key constraint on table 'fmj_crf/projet'.   
  Incorrect options in FOREIGN KEY constraint 'test2/FMJ_CRF/PROJET_CUSTOMER_FK_ID_CUSTOMER_ENTITY_ENTITY_I  
  D'

Anyone can help ?

Best Answer

I found a solution by changing the definition to the foreign key column in the upgradeSchema file like this :

$setup->getConnection()->changeColumn(
                    'fmj_crf/projet', 'customer_fk_id', 'customer_fk_id', ['type' => Table::TYPE_INTEGER, 'unsigned' => true, 'nullable' => false, 'primary' => true,]
            );

and then :

$setup->getConnection()->addForeignKey(
                    $setup->getFkName(
                            'fmj_crf/projet', 'customer_fk_id', 'customer_entity', 'entity_id'
                    ), $setup->getTable('fmj_crf/projet'), 'customer_fk_id', $setup->getTable('customer_entity'), 'entity_id', \Magento\Framework\DB\Ddl\Table::ACTION_CASCADE
            );
Related Topic