Magento 2 compound primary keys
I recently tried to use compound primary keys in Magento 2. I got different problems with this and ended up with a solution, that is not optimal. In case of Magento, the only possible one. Here is all you need to know to implement custom models for tables with compound primary keys.
Magento 2 compound primary keys
If you learned good database table creation, you should know compound primary keys. This are primary keys, that are combinations of two or more colum. A very simple example is a N to N relationship between two tables. If you have such a construct, you need a third table with a compound primary key with both foreign keys. Think of a table that defines colors and your product table. A product can have multiple colors and one color can be used by different products:
If you want to use this model as Magento collection, you need to define it. In you module model folder you create a ResourceModel like this:
1 2 3 4 5 6 7 8 9 10 11 12 13 | namespace MyCompany\MyModule\Model\ResourceModel; class ProductColor extends \Magento\Framework\Model\ResourceModel\Db\AbstractDb { /** * Initialize resource model * * @return void */ protected function _construct() { $this->_init('product_color', INSERT_HERE); } } |
You will have a problem! You need to define your primary key and insert it into INSERT_HERE as param. There is only an option for a single column. You can not use both primary key columns. If you only define one of them, you will get an error if you save a new model with an already existing value of that column or if there is already data in this table, you will get an error if you get a collection of product_color models. This resource model is not very useful!
Magento’s Mage_Core_Model_Abstract was not build for such database table designs. If your models are based on this you have no other solution. The only other solution that may be worth thinking of is to rewrite your own abstract model that uses Zend Framework. But this is no quick solution.
Solution
The only possible, but not optimal solution is to create a new column with an auto_increment value that acts as a new primary key. You can use this column name in your resource model and input its name into INSERT_HERE. This works, but it is now possible to insert corrupt data. Instead of updating already existing values it now inserts new lines. The only way to resolve this is by additional validation and code in your module. This is bad database design!
Conclusion
You can not use compound primary keys with Magento 2. You should design your database tables with this in mind. The only solution is a new auto_increment primary key.
Do you have a better solution, or any thoughts about this?
