Soft delete followed by hard delete when database has foreign key can fail

[reedstonefood]

When the table has a foreign key relationship defined on it, paranoia struggles when soft deletion is followed by hard deletion.

Consider these two table schemas, with one row in each where both acts_as_paranoid. We want to soft delete both rows, and then at a later time hard delete both rows.

parent:
  id INTEGER,
  deleted_at DATETIME

child:
  id INTEGER,
  foreign_id INTEGER,
  deleted_at DATETIME,
  FOREIGN KEY(foreign_id) REFERENCES parent(id)

The problems encountered differs depending on the dependent relationship defined on the has_one/belongs_to relationship defined in the model. They both have workarounds but it would be better if paranoia could handle these situations natively.

Scenario 1 - dependent: :nullify

The order in which the two records are deleted leads to different data.

• If the parent is deleted first, then child.foreign_id is set to NULL
• If the child is deleted first, then child.foreign_id is NOT set to NULL

If this sequence of events happens:

• child is soft-deleted
• parent is soft-deleted (OPTIONAL STEP - makes no difference)
• parent is hard-deleted

...then the deletion fails because the ID of the parent still exists in child.foreign_id.

ActiveRecord::InvalidForeignKey: SQLite3::ConstraintException: FOREIGN KEY constraint failed

The workaround is to soft-delete the child first, this means there is no longer a foreign key relationship and thus the parent can be deleted.

Alternatively, hard-deleting the child before hard-deleting the parent works, for the same reason.

Reproduction:

# in setup!
'paranoid_model_with_foreign_key_selves' => 'self_parent_id INTEGER, deleted_at DATETIME, FOREIGN KEY(self_parent_id) REFERENCES paranoid_model_with_foreign_key_selves(id)',

# test
  def test_destroy_self_foreign_key
    self_parent = ParanoidModelWithForeignKeySelf.create
    self_child = ParanoidModelWithForeignKeySelf.create(self_parent:)

    self_child.destroy
    self_parent.destroy

    self_parent.reload.really_destroy!
    self_child.reload.really_destroy!  
   
    assert_equal 0, ParanoidModelWithForeignKeySelf.with_deleted.count
  end

# model setup

class ParanoidModelWithForeignKeySelf < ActiveRecord::Base
  acts_as_paranoid
  belongs_to :self_parent, :class_name => "ParanoidModelWithForeignKeySelf", :foreign_key => :self_parent_id, optional: true
  has_many :self_children, :class_name => "ParanoidModelWithForeignKeySelf", :foreign_key => :self_parent_id, dependent: :nullify
end

Scenario 2 - dependent: :destroy

In this scenario, it doesn't matter which way round you soft-delete the two records, the data is the same. The foreign key remains populated.

The problem then comes if you try and delete the child record. This throws a ActiveRecord::RecordNotFound error as it tries to find a matching parent record. There is one, but as it is soft-deleted it is not found by the paranoia code.

This can be reproduced in the paranoia test suite by amending this line in the setup! method:

'paranoid_model_with_has_one_and_builds' => 'parent_model_id INTEGER, color VARCHAR(32), deleted_at DATETIME, has_one_foreign_key_id INTEGER , FOREIGN KEY(has_one_foreign_key_id) REFERENCES paranoid_model_with_foreign_key_belongs(id)',

Then adding this test:

def test_destroy_soft_then_hard_with_foreign_key_dependent_destroy
    parent = ParanoidModelWithForeignKeyBelong.create
    child = ParanoidModelWithHasOne.create(paranoid_model_with_foreign_key_belong: parent)

    parent.destroy
    child.destroy

    child.reload.really_destroy!
    parent.reload.really_destroy!
    
    assert_equal 0, ParanoidModelWithForeignKeyBelong.with_deleted.count
    assert_equal 0, ParanoidModelWithHasOne.with_deleted.count
  end

When running child.reload.really_destroy it throws:

ActiveRecord::RecordNotFound: Couldn't find ParanoidModelWithForeignKeyBelong with [WHERE "paranoid_model_with_foreign_key_belongs"."id" = ?]

The workaround is to really_destroy the parent first.