Magento 2 create foreign key with upgrade schema

Question

I have 2 table. I want to createForeignKey. I have my code:

if(version_compare($context->getVersion(), '1.2.0', '<')) {
        $installer->getConnection()->addForeignKey(
            $installer->getFkName('course_courses', 'courses_id', $installer->getTable('course_participant'), 'courses_id'),
            $installer->getTable('course_courses'),
            'courses_id',
            $installer->getTable('course_participant'),
            'courses_id',
            \Magento\Framework\DB\Ddl\Table::ACTION_NO_ACTION
        );
    }

Please help me!

Answer 1

Try this:

->addForeignKey(
      $installer->getFkName('<ChildTable>', 'entity_id', '<ParentTable>', 'entity_id'),
      'entity_id',
      $installer->getTable('<ParentTable>'),
      'entity_id',
      \Magento\Framework\DB\Ddl\Table::ACTION_CASCADE
)

Note: Both table has same field and set primary key to parent table.

You are adding Foreign key to wrong table, You can not set customer_grid_flat table's entity_id as foreign key until you set primary key to customer_entity_varchar table's entity_id.

And read more information:-

Magento 2 How to add foreign key in update schema

Hope this help you

Thanks ...

Answer 2

Try below code

if(version_compare($context->getVersion(), '1.2.0', '<')) {
    $installer->getConnection()->addForeignKey(
        $installer->getFkName('course_courses', 'courses_id','course_participant','courses_id'),
        $installer->getTable('course_courses'),
        'courses_id',
        $installer->getTable('course_participant'),
        'courses_id',
        \Magento\Framework\DB\Ddl\Table::ACTION_NO_ACTION
    );
}

Check if setup Version in your module.xml file with tag setup_version = 1.2.0 ? And in your database check if setup_module search your modulename entry have vesion less than 1.2.0 ?than only it will run upgradeschema file.

Answer 3

Your code is correct. But the primary key of your parent table is 'id'.

if(version_compare($context->getVersion(), '1.2.0', '<')) {
    $installer->getConnection()->addForeignKey(
        $installer->getFkName('course_participant', 'courses_id', $installer->getTable('course_courses'), 'id'),
        'course_participant',
        'courses_id',
        $installer->getTable('course_courses'),
        'id',
        \Magento\Framework\DB\Ddl\Table::ACTION_NO_ACTION
    );
}