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

Question

I'm working with Rails and PostgreSQL and have a basic one-to-many relationship going on, one Auction has many Bids. However when I try and delete an auction (that has bids present) I get the following error:

ERROR: update or delete on table "auctions" violates foreign key constraint "fk_rails_43e9021cbf" on table "bids". DETAIL: Key(id)=(1) is still referenced from table "bids".

Deleting auctions with no bids gives no error.

The part that confuses me is that inside my Auction model, I have:

has_many :bids, dependent: :destroy

Since I have a dependent destroy clause, why am I still getting this error?

EDIT: I've tried dropping the whole DB, then recreating/re-migrating everything - still get the same error.

Answer 1

From Rails v4.2 you can do this:

Create a migration to update the foreign keys

20160321165946_update_foreign_key.rb

class UpdateForeignKey < ActiveRecord::Migration
  def change
    # remove the old foreign_key
    remove_foreign_key :posts, :users

    # add the new foreign_key
    add_foreign_key :posts, :users, on_delete: :cascade
  end
end

Answer 2

Are you using delete or destroy to remove the objects? I think you are using delete and you want to use destroy

See http://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html#module-ActiveRecord::Associations::ClassMethods-label-Delete+or+destroy-3F

Answer 3

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

Answer 4

Are you by chance using the paranoia gem or something like it?

If you are bids are paranoid and auctions are not, you may run into this error.

This would happen because when rails executes the dependent: destroy, it would soft-deletes the bids, but they still actually exist in the DB (they just have the deleted_at column set). Therefore, the foreign key constraint would fail.

Answer 5

Your error is from the database not rails. You need to delete the bids first in your app or change the foreign key constraint in the db to cascade the delete

Answer 6

Other answers are good, but don't mention that sometimes you want to leave the dependent record, but nullify the foreign key.

class Post < ActiveRecord::Base
  has_many :comments, dependent: :nullify
end

Note that this will require ensuring the foreign key column in the database table has null: true

I'm not positive, but you may also need to add optional: true to the belongs to association defined in the dependent model.

Answer 7

Marc Busqué has a very good article about this problem that might can help.

"When ActiveRecord encounters a foreign key violation, it raises an ActiveRecord::InvalidForeignKey exception. Even if in its documentation it just says that it is raised when a record cannot be inserted or updated because it references a non-existent record, the fact is that it is also used in the case we are interested."

With that and a rescue_from we can just add to ApplicationController or to a controller concern:

rescue_from 'ActiveRecord::InvalidForeignKey' do
  # Flash and render, render API json error... whatever
end

Answer 8

Here explain how to fix it.

To solve this, you probably want to modify your has_many :comments association in your User model to have a dependent option. Some possibilities:

• has_many :comments, dependent: :delete_all - just automatically delete them when the user is deleted
• has_many :comments, dependent: :destroy - like above, but call #destroy on each comment instead of just deleting directly in the db
• has_many :comments, dependent: :nullify - don't delete comments when the user is deleted, just null out their user_id column

See http://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html#method-i-has_many for more information

Answer 9

A really simple explanation: the associated table contains at least 1 record joined to the record in the table you're trying to destroy.

To fix this, add dependent: :destroy (assuming a User has_many Posts)

has_many :post, dependent: :destroy