We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. ORM

Delete failed: Relation action cascade not working when database fk are present

Hi I have two database tables.

Table A has many Table B's . Table B's belong to Table A. This relationship is defined in the databases real foreign keys. This relationship is defined in ORM using virtual foreign keys.

In the Model for Table A I have defined:

 $this->hasMany("id", "TableB", "table_a_id", array(
            "foreignKey" => array(
                "action" => Relation::ACTION_CASCADE,
                "message" => "Cannot delete table a as it still contains table b's"
            )
        ));

When I attempt to tableA->delete() it fails because of an integrity contraint defined in the database. The integrity constraint it fails on is:

    Name            Schema      Table       Column      Ref-Table   Ref Column
    table_a_fk      my_schema   table_b     table_a_id  table_a     id

Can anyone confirm that:

You can only delete a parent record that has a relation cascade on its children providing you do not have real foreign keys defined in the database?

Many Thanks.



125.8k

Setting up a Phalcon relation with a cascade action allows Phalcon to mimic the behaviour of a foreign key. It needs to use 2 queries to delete both the parent & child row. If you don't need to take special action when the child object is deleted, then I wouldn't bother with setting up the action. If you do need to take special action - well then I'm not sure what to do.



47.7k

Can you give an example of 'special action'?

My aim is to soft delete all records on tableA->delete() .



125.8k

By "special action" I mean if you want to do something additional when the child object is deleted. An example could be an email. Say you want to get an email every time a child object is deleted - that's something you could put in the beforeDelete() method of the child object. Relying on the database's foreign key constraints wouldn't allow you to do that.

If you want to soft delete, then you're not really deleting at all. This problem doesn't exist because a soft delete is really just an update.



47.7k
edited Aug '15

The special action I have in mind is to add the behaviour SoftDelete.

So I have Trait which defines beforeDelete() and is not declared anywhere else using the Trait or by another Trait :

    trait MyTrait {

    public function beforeDelete() {
          $this->addBehavior(new \Phalcon\Mvc\Model\Behavior\SoftDelete(
                          array(
                      'field' => 'status',
                      'value' => 'D'
                          )
                  ));
          }

    }

Then in the initialize for the parent I define the relation:

  $this->hasMany("id", "Children", "parent_id", array(
              "action" => Relation::ACTION_CASCADE,
              "message" => "Parent cannot be deleted has it still has children."
          ));

Then when I:

$parent->delete();

What I find is that the parent record is soft deleted (status="D") but the delete doesn't cascade down to the Children table which also uses soft delete.

So it appears as if all the Relation::ACTION_CASCADE is really doing is triggering beforeDelete() in the parent when as I am expecting it to cascade to the children as implied.

When I take out the Relation::ACTION_CASCADE away then I find beforeDelete doesn't get triggered at all.



125.8k

The SoftDelete behaviour is something that needs to be defined in initialize(), not beforeDelete() - both in the parent and child objects. Setting up your relation to cascade like you have, should trigger the behaviour on the child as well.



47.7k
edited Aug '15

Ok . Now I have put the softDelete behaviour in the initialize of each Model. That rules out putting softDelete in my Trait.

Then I have tried:

$parent->delete();

I found only the parent softDeleted. Nothing happened to the children. It did not cascade as it would for a hard delete.

I think I am just frustrated that softDelete does not appear to be completely hooked up.



125.8k

That's unfortunate it doesn't cascade. Have you tried putting $children->delete() in the afterDelete() method of the parent?



47.7k

I'll try it and edit this post when I get to it.