1

While I used InnoDB tables I caught FK constraint exceptions from InnoDB engine.

I know that Yii can fetch related tables according to relations(). Besides I need to check referential integrity during other CRUD operations - such as insert, update and delete.

Is there some Yii native mechanism to perform this? Or I should write additional code for this checking?

Community
  • 1
  • 1
Peter Lyashevich
  • 35
  • 1
  • 3

1 Answers1

1

In order to avoid errors from the database you need to write additional code to handle this. I manage it using the beforeDelete() method of my models. Write an if condition to check for referential integrity violations uising the model's relations. You can then either delete the offending records or abort the deletion by not returning 'parent::beforeDelete().'

In this many to many example from a User model we can wrap the 'return parent::beforeDelete()' call in an if to check the relations() of the model in the following way:

 protected function beforeDelete() {
            if (empty($this->tasks))
            return parent::beforeDelete();
        }

The deletion is aborted if the User is found to have any assigned tasks.

For reference the relation in the model would look like this and requires a third table:

'tasks' => array(self::MANY_MANY, 'Task', 'task_assignment(user_id,task_id)'),

This example is very basic and obviously you may want to implement a more complex way of handling these scenarios which could include deletion of related records or even the updating of them to remove their foreign key references. You will have to decide what works best and makes sense for each of your models and their relations.

ShaunUK
  • 929
  • 6
  • 17
  • 2
    You may also want to look into soft delete options that mean the records are never actually removed from the database. – ShaunUK Apr 05 '13 at 13:48
  • tnx a lot. I've already implemented this using hooks as you suggested. – Peter Lyashevich Apr 06 '13 at 07:47
  • 1
    A helpful addition to make things more efficient: Create a relation `'tasksCount'=>array(self::STAT, 'TaskAssignment', 'user_id'),` and change the check in `beforeDelete` to `if(!$this->tasksCount)`. This way, you don't need to actually query and load all the tasks to perform this check, as this new relation will simply return an integer count of the number of records in the database. – Willem Renzema Apr 06 '13 at 15:50
  • Willem is right, it's more efficient to use an extra relation like that to perform a COUNT query. Great tip. – ShaunUK Apr 08 '13 at 09:13