Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
reedstonefood opened this issue Feb 14, 2025 · 0 comments

Comments

@reedstonefood
Copy link
Contributor

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant