Composite foreign key


I have a table called invoice_item. This table has a composite foreign key of "entity_id" and "table_name". In MySQL, and most other relational databases, you can create a foreign key to reference a composite primary key like so…

FOREIGN KEY (`entity_id`,`table_name`) 

REFERENCES invoice_item(`entity_id`,`table_name`)

This is a very important piece of functionality that many database model use.

How can this be achieved using a standard Yii2 migration script method? Also, how would I create the relationship "get" method in the ActiveRecord?

For the migration:

Have you tried specifying multiple keys in hasMany?

Thanks for the reply Patrik…

I have been looking for this information all over the place but it was right under my nose all along. I would like to document the rest of this process for anyone in the future that runs into this same scenario.

Just like the API docs said, I successfully created the composite foreign key. Using the previous example, it would look like this…

$this->addForeignKey('fk_composite_example', '{{%invoice_item}}', ['entity_id','table_name'], '{{%parent_invoice_item}}', ['entity_id','table_name'], 'CASCADE', 'CASCADE' );

The AR relationship methods need to look like this…

return $this->hasMany(InvoiceItem::className(), ['entity_id' => 'entity_id', 'table_name'=>'table_name']);           

I hope this helps anyone looking to do the same thing.