Thursday, June 24, 2010

Adding Foreign Keys Manually

Using MyISAM tables (in MySQL), I'm unable to set foreign keys in the database. However, I'm working with several tables which utilize foreign key type relationships. The problem was how to get Yii to recognize those relationships properly.

This one took me a while to figure out. With the help of the experts over at the Yii Forums, I was able to put together this solution.

(Not going to debate the wisdom of the setup of the scenario, it's just an example :p)

Table 1- Messages (items displayed to users) - identified publicly by "sid"
Table 2- Clicks (tracks clicked messages) - records the message "sid" on receipt, not it's id.

In the clicks table, override the getTableSchema() method:

public function getTableSchema()
  $table = parent::getTableSchema();
  $table->columns['m_sid']->isForeignKey = true;
  $table->foreignKeys['m_sid'] = array('Message', 'sid');

  return $table;  

Now the standard relation definitions work directly!

This is the relation in the Message model:
'clicks'=>array( self::HAS_MANY, 'Click', 'm_sid' ),

as well as this relation in the Click model:
'message'=>array( self::BELONGS_TO, 'Message', 'sid'),