Composite foreign key

Yii 2.0 General Discussions
1

Hello.

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?

2

For the migration: http://www.yiiframework.com/doc-2.0/yii-db-migration.html#addForeignKey()-detail

Have you tried specifying multiple keys in hasMany?

http://www.yiiframework.com/doc-2.0/yii-db-baseactiverecord.html#hasMany()-detail

3

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.