12

Tried to use foreign keys with delete cascade and softDeletes without much luck.

I have 2 tables: Users, Events. Both tables have softDeletes.

Users can have 0..n Events.
Events have an user_id, used as foreign key on users, like this:

$table->foreign('user_id')->references('id')->on('users')->onDelete('CASCADE')->onUpdate('CASCADE');

Problem is, when I delete an User, it gets soft-deleted, but its Events do not - either soft deletion or physical deletion.

Am I doing something wrong, or is this the correct Eloquent behavior?

Secondly, if that is the correct behavior, how to best implement deletion cascade? maybe overriding the delete() method in my Models like this ...

public function delete()
{
  //delete all events...
  __parent::delete()
}

?

Antonio Carlos Ribeiro
  • 86,191
  • 22
  • 213
  • 204
RedSash
  • 169
  • 2
  • 10

3 Answers3

11

The DB's foreign key won't do anything because you haven't changed the primary key in question. Only if you update or delete the primary key will the related rows be modified.

From everything I can find about this topic, the solution is to use Eloquent's Model Events to listen for a delete event, and update the related tables.

Here's one StackOverflow question about it.

Alternatively, you can "extend" the delete() method and include the functionality directly as well. Here's an example.

Community
  • 1
  • 1
Aken Roberts
  • 13,012
  • 3
  • 34
  • 40
  • Cryode, I tried the Model Events and it works - but partially. Let me explain. The real situation is 3 tables: Users, Events, Comments. Events have 0..n Comments. Following [link](http://stackoverflow.com/questions/17243637/laravel-4-cascading-soft-deletes), I added logic in the boot() method to both Users and Events. Now: softDelete Event, Expected: softDelete Comments, Result: OK; softDelete User, Expected: softDelete Events, Result: OK, softDelete Comments to those events, result: KO. It seems that the deletions do not propagate beyond the first "level".. – RedSash Aug 01 '13 at 08:23
  • 1
    This is the solution that I have implemented myself. I first draw out a map of the relationships that all my models have with one another and then write delete methods to extend eloquent within my models that should cascade down deletes. Finally as this is an easily cocked up task I write unit-tests to show that the cascade effect only effects what it aught to. This way Users delete calls UserRecords Delete which then calls UserRecordsMeta delete and so on. – carbontwelve Aug 09 '13 at 08:47
2

You're overthinking this.

Either just delete the events right before you delete the users:

$user->events()->delete();
$user->delete();

Or create a customer delete function in the user model:

public function customDelete(){
    $this->events()->delete();
    return $this->delete();
}

You could also add a model observer and watch for the deleting or delete event, but in the scenario you mentioned above, the previous two methods would be a more simple solution.

http://laravel.com/docs/4.2/eloquent#model-observers

Jeremy Gehrs
  • 413
  • 6
  • 17
0

If I understand correctly, you are trying to cascade softdeletes in both tables?

I believe to do this with ON UPDATE CASCADE is not the correct approach. I'll try to explain why...

To even attempt to do this you need to create a relationship of foreign key to composite key.

ie you need to link the (events.user_id and deleted_at) to (user.id and delete_at). You change one, it'll update the other.

First you will need to add a default rule to your deleted_at columns, as you can not link on null values.

So add to your migrations for both tables... $table->softDeletes()->default('0000-00-00 00:00:00');

Add to your user table a unique key using 'id' and 'deleted_at'

Schema::table('users; function($table) { $table->unique(array('id','deleted_at')) });

Then in the events table create a foreign key like so (links to the unique key)

Schema::table('events; function($table) { $table->foreign(array('user_id','deleted_at'),'events_deleted_at_foreign_key')-> }->references(array('id','deleted_at'))->on('users')->onUpdate('CASCADE'));

Run this, you should now find if you soft delete your user, it will soft delete its' events.

However if you now try to soft delete an event, it will fail on the foreign key restraint. Why you might ask!?

Well what you're doing is creating a Parent Child relationship using id,deleted_at in both tables. Updating the parent, will update the child. And the relationship is unbroken. However if you Update the child, the relationship is now broken, leaving the child as an orphan in the table. This fails the foreign key restraint.

Sooo a long winded answer, but hopefully a good explanation of why what you're trying to do won't work and save you a whole lot of time trying to do this with ON UPDATE CASCADE. Either get in to the TRIGGERS, and TRIGGER a function to handle what you're trying to do, or handle it in your application. Personally I'd do it with TRIGGERS so the database remains it's own entity and not having to rely on anything to keep data integrity. 

delimiter //

CREATE TRIGGER soft_delete_child AFTER UPDATE ON db.users FOR EACH ROW BEGIN IF NEW.deleted_at <> OLD.deleted_at THEN UPDATE events SET deleted_at=NEW.deleted_at WHERE events.user_id=NEW.id; END IF; END;

// delimiter ;

Marc Hussey
  • 1
  • 1