Rails: Violates Foreign Key Constraint

ERROR: delete on table violates foreign key constraint. Key id is still referenced from table (many)

My issue was that i am using @auction.delete (visible in the screenshot I posted) when trying to remove a record.

Delete will ignore any callbacks I have in place. So even though I have a dependent destroy clause, it is not being called - hence Rails is throwing an error. If/When I changed the code to read @auction.destroy, the call-back got invoked and it solved the problem.

Reference:
Difference between Destroy and Delete

update or delete on table x violates foreign key constraint fk_rails_5a7b40847a on table x

It depends what you want to do with the post's comments on its deletion. In case you want to delete them on cascade, for example:

post.rb

has_many :comments, dependent: :destroy

Deleting Record violates foreign key constraint

Your User should use the :dependent option:

class User < ApplicationRecord
  # ...
  has_many :notifications, foreign_key: :notified_by_id, dependent: :destroy
end

The :foreign_key should also be specified if you used :class_name on the belongs_to side of the association.

Now, when you delete a User, all of the notifications referencing them are also deleted.

You can repeat the same process for the Article or any other model referenced.

PG::ForeignKeyViolation: ERROR: update or delete on table xxx violates foreign key constraint

From the fine manual:

has_many(name, scope = nil, options = {}, &extension)

[...]

• :dependent
  Controls what happens to the associated objects when their owner is destroyed. Note that these are implemented as callbacks, and Rails executes callbacks in order. Therefore, other similar callbacks may affect the :dependent behavior, and the :dependent behavior may affect other callbacks.
  
  
  • :destroy causes all the associated objects to also be destroyed.
  • :delete_all causes all the associated objects to be deleted directly from the database (so callbacks will not be executed).
  • [...]

So :delete_all does take care of foreign keys but, since no callbacks are invoked, it only goes one level deep. So this in Company:

has_many :projects, dependent: :delete_all

means that calling #destroy on a company will directly delete the associated projects from the database. But that won't see this:

has_many :tasks, dependent: :delete_all

that you have in Project and you end up trying to delete projects that are still referenced in tasks as the error message indicates.

You could switch all your associations to dependent: :destroy, this will pull everything out of the database before destroying them and callbacks will be called (which will load more things out of the database only to destroy them which will load more things out of the database...). The end result will be a lot of database activity but all the foreign keys will be properly followed.

Alternatively, you could put the logic inside the database where it usually belongs by specifying on delete cascade on the foreign key constraints:

CASCADE specifies that when a referenced row is deleted, row(s) referencing it should be automatically deleted as well

Your add_foreign_key calls would look like:

add_foreign_key "projects", "companies", on_delete: :cascade
add_foreign_key "tasks", "projects", on_delete: :cascade
add_foreign_key "task_times", "tasks", on_delete: :cascade

in this case. You'd probably want to leave the dependent: :delete_alls in your models as a reminder as to what's going on, or you could leave yourself a comment.

Foreign key constraint error when deleting record

You can avoid deleting the records by switching the dependent link type:

has_many :posts, dependent: :nullify

Which flips the column to NULL and breaks the link. This also erases any information as to who created those posts.

As a note, in systems I design it's often important to retain information on deleted users, so it's often the case that they're not actually deleted, just flagged as deleted and made invisible by use of scopes and other tests. You can also cascade the deletion of the user into the same soft deletion of their posts.

Check your legal requirements for retention of information before arbitrarily deleting records. You may need to retain these for a period of time, in which case a deleted_at field can help. If that's older than N days, where that's the legal minimum you must retain them, you can safely purge the records.

Rails: violates foreign key constraint

Add dependent: :destroy option to your has_many definitions.

Check docs

Yet better option to respect data integrity is to set the CASCADE DELETE on the database level: say, you have comments table and users table. User has many comments You want to add a foreign_key to table comments and set deleting the comment whenever the user is destroyed you would go with the following (the on_delete: :cascade option will ensure it):

add_foreign_key(
  :comments,
  :users,
  column:
  :user_id,
  on_delete: :cascade
)

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

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

PG::ForeignKeyViolation: ERROR: update or delete on table

Ok, the user want to set to null the reference in licenses when a user is deleted, but does not want to touch the schema. I think a dependent: :nullify on the has_many association for admins (application level) might be the solution

# admin
has_many :dont_know_the_association, dependent: :nullify

---

Related Topics