ERROR: delete on table violates foreign key constraint. Key id is still referenced from table (many)
Ruby on-RailsPostgresqlSublimetext2Better Errors-GemRuby on-Rails Problem Overview
I'm working with Rails and PostgreSQL and have a basic one-to-many relationship going on, one Auction
has many Bid
s. 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.
Ruby on-Rails Solutions
Solution 1 - Ruby on-Rails
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
Solution 2 - Ruby on-Rails
Are you using delete
or destroy
to remove the objects? I think you are using delete
and you want to use destroy
Solution 3 - Ruby on-Rails
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: https://stackoverflow.com/questions/22757450/difference-between-destroy-and-delete
Solution 4 - Ruby on-Rails
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.
Solution 5 - Ruby on-Rails
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
Solution 6 - Ruby on-Rails
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
Solution 7 - Ruby on-Rails
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.
Solution 8 - Ruby on-Rails
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