Deleting rails active records with delete_all/delete violates foreign key constraint

Question

I have an active record association setup with :dependent => :destroy, which works as intended. Then I found out I need to use delete instead of destroy due to performance, so I just changed destroy to delete_all/delete depending on the association.

When I try to delete:

shop.shop_snapshots.completed.last.delete

I get the error message:

ActiveRecord::InvalidForeignKey (PG::ForeignKeyViolation: ERROR:  update or delete on table "shop_snapshots" violates foreign key constraint "fk_rails_c24b24adaf" on table "inventory_items"

But why is that - I believe I have the proper setup on the snapshot:

has_many :inventory_items, :dependent => :delete_all

and it worked for destroy, so what am I doing wrong?

Thanks /Louise

Does this answer your question? [Difference between Destroy and Delete](https://stackoverflow.com/questions/22757450/difference-between-destroy-and-delete) — Roman Alekseiev, Jan 31 '20 at 10:25
Not quite - it says "delete will only delete current object record from db but not its associated children records from db". That would mean that only my snapshot is deleted, and not the associated inventory_items, which explains the error message. Bu why does Rails support setting :dependent => :delete_all, if it cannot support cascade deletes on children - the ":dependent => :delete_all" would have no effect then? — Lull, Jan 31 '20 at 10:35
if you call `destroy` instead of `delete` it will work, I am sure — Roman Alekseiev, Jan 31 '20 at 10:40
Yes using `destroy` already works as I described - but I do not want to use `destroy` as I need the better performance provided by `delete`. — Lull, Jan 31 '20 at 11:43
Please read the text linked in the first comment again: only `destroy` takes the `:dependent` association actions into account. `delete` can only offer better performance _because_ it skips callbacks and dependent asssociation checks. — nathanvda, Jan 31 '20 at 14:01

max · Accepted Answer · 2020-02-03T06:07:28.720

On Postgres you can use the CASCADE option on the foreign key itself.

CASCADE specifies that when a referenced row is deleted, row(s) referencing it should be automatically deleted as well.
- https://www.postgresql.org/docs/9.5/ddl-constraints.html

This is usually setup when creating the table but you can add it to an exisiting table by removing and then re-adding the foreign key constraint:

class AddCascadeToOrderItems < ActiveRecord::Migration[6.0]
  def up
    remove_foreign_key :order_items, :orders
    add_foreign_key :order_items, :orders, on_delete: :cascade
  end

  def down
    remove_foreign_key :order_items, :orders
    add_foreign_key :order_items, :orders
  end
end

Since this is handled on the DB level no configuration is needed in your model.

has_many :inventory_items, dependent: :delete_all

Works as well and is the only option on peasant databases like MySQL but it will only be fired when you call .destroy and not .delete on the model that declares the association as its implemented as a model callback. For example:

class Store < ApplicationRecord
  has_many :inventory_items, dependent: :delete_all
end

store = Store.find(1)
store.destroy # triggers callbacks and will delete all assocatiated inventory_items
store.delete  # will not trigger callbacks

Ok thanks, your answer helped me a lot. A couple of follow-up questions: a) in the down method the remove and add methods should be switched right? b) ok I see the dependent: :delete_all works if I initiate it with destroy, but it does only work on "one level", i.e. any children order_items might have will not be deleted, because the callback is not triggered by the delete on order_items as you explain. For that reason I will need to use cascade on the foreign keys. — Lull, Feb 02 '20 at 19:11
A) yes you're right - the order is wrong. B) yes. Also right. The associated records are removed with the `.delete_all` method which just creates a single SQL delete query and does not instanciate the models or call callbacks. — max, Feb 03 '20 at 06:06
The advantage of cascade inte this case is that it cascades so the children and grandchildren will be deleted if you setup the foreign keys properly. — max, Feb 03 '20 at 06:10

score 0 · Answer 2 · answered Jan 31 '20 at 11:57

0

You need to set it on migration level.

Something like this:

create_table :childs do |t|
  t.references :parent, index: true, foreign_key: {on_delete: :cascade}
  t.string :name

  t.timestamps null: false
end

answered Jan 31 '20 at 11:57

Roman Alekseiev

1,854
16
24

Deleting rails active records with delete_all/delete violates foreign key constraint

2 Answers2