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

Question

I have several tables that have foreign key constraints associated with them, each referencing the other in a hierarchical fashion as outlined below.

When I try to destroy a Company that has at least 1 Project, that has at least 1 Task, that has at least 1 TaskTime like so...

irb(main):014:0> Company.first.destroy

I get the below output and error. I am under the impression now that simply having dependent: :delete_all doesn't deal with the foreign key constraints, is this true? If so, how do I deal with this scenario? I know about the before_destroy callback, do I have to use it in this case? If so, how do I temporarily disable the foreign key constraints in order to destroy all the associated rows down the line? What's making this even more confusing is that I have an old rails project that has the same table/model set up only it's a single model_a has_many model_bs, dependent: delete_all relationship with a foreign key constraint and I can ModelB.destroy_all and it works, so I don't get it. I've also read posts where setting cascading deletes on the table works and some posts saying that this need not be done if you handle it yourself in code; I'd like to handle this in my code if the solution isn't too hairy.

Company Load (0.4ms)  SELECT  "companies".* FROM "companies" ORDER BY 
                              "companies"."id" ASC LIMIT $1 [["LIMIT", 1]]
   (0.2ms)  BEGIN
             SQL (0.9ms)  DELETE FROM "projects" 
                          WHERE "projects"."company_id" = $1 [["company_id", 3]]
   (0.1ms)  ROLLBACK
             Traceback (most recent call last):
   1: from (irb):13
             ActiveRecord::InvalidForeignKey (PG::ForeignKeyViolation: ERROR:  update or delete on table "projects" violates foreign key constraint "fk_rails_02e851e3b7" on table "tasks"
                          DETAIL:  Key (id)=(4) is still referenced from table "tasks".
                        : DELETE FROM "projects" WHERE "projects"."company_id" = $1)

Schema

# /db/schema.rb

create_table "companies", force: :cascade do |t|
...
end

create_table "projects", force: :cascade do |t|
...
end

create_table "tasks", force: :cascade do |t|
...
end

create_table "task_times", force: :cascade do |t|
...
end
...

add_foreign_key "projects", "companies"
add_foreign_key "tasks", "projects"
add_foreign_key "task_times", "tasks"

Models

# /models/company.rb

class Company < ApplicationRecord
  has_many :projects, dependent: :delete_all
...
end

# /models/project.rb

class Project < ApplicationRecord
  has_many :tasks, dependent: :delete_all
...
end

# /models/task.rb

class Task < ApplicationRecord
  has_many :task_times, dependent: :delete_all
...
end

# /models/task_time.rb

class TaskTime < ApplicationRecord
...
end

Burger

Also mind running .delete on an object does not run callbacks (like dependent: ). These are run when one calls .destroy. – Efflorescence

Answer 1

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.

Imbalance

And that’s why my old app works...because it’s only one level deep! Ty. – Burger

Answer 2

I had the same problem and accomplished what I needed by simply destroying the records in the associated table first, and then destroying the main table. That way it was not violating referential integrity and there was consequently no error.

I got this answer from this question

Eigenvalue