PG::ForeignKeyViolation:错误:更新或删除表“xxx"；违反外键约束 [英] PG::ForeignKeyViolation: ERROR: update or delete on table "xxx" violates foreign key constraint

问题描述

我有几个表具有与其关联的外键约束，每个表都以分层方式引用另一个，如下所述.

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

当我试图摧毁一个至少有 1 个项目、至少有 1 个任务、至少有 1 个任务时间的公司时......

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

我得到以下输出和错误.我现在的印象是，仅仅拥有 dependent: :delete_all 并不能处理外键约束，这是真的吗?如果是这样，我该如何处理这种情况?我知道 before_destroy 回调，在这种情况下我必须使用它吗?如果是这样，我如何暂时禁用外键约束以销毁所有关联的行?更令人困惑的是，我有一个旧的 Rails 项目，它设置了相同的表/模型，只是它是一个 model_a has_many model_bs,dependent: delete_all 与外键约束的关系，我可以ModelB.destroy_all 并且它有效，所以我不明白.我还阅读了在表上设置级联删除有效的帖子，以及一些帖子说如果您自己在代码中处理它就不需要这样做；如果解决方案不太复杂，我想在我的代码中处理这个问题.

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)

架构

# /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/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

推荐答案

来自 精美手册:

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

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

• :依赖
  控制关联对象的所有者被销毁时会发生什么.请注意，这些是作为回调实现的，Rails 会按顺序执行回调.因此，其他类似的回调可能会影响:dependent 行为，而:dependent 行为可能会影响其他回调.
  • :destroy 导致所有关联的对象也被销毁.
  • :delete_all 导致直接从数据库中删除所有关联的对象(因此不会执行回调).
  • [...]
  • :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).
    • [...]

    所以 :delete_all 确实会处理外键，但是由于没有调用回调，所以它只会深入一层.所以这在 Company 中:

    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
    

    表示对公司调用#destroy会直接从数据库中删除关联的projects.但那不会看到这个:

    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
    

    您在 Project 中拥有的项目，而您最终尝试删除在 tasks 中仍被引用的项目，如错误消息所示.

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

    您可以将所有关联切换到 dependent::destroy，这将在销毁它们之前将所有内容从数据库中拉出并调用回调(这将从数据库中加载更多内容，仅用于销毁它们，这将从数据库中加载更多的东西......).最终结果将是大量的数据库活动，但将正确遵循所有外键.

    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 指定当引用的行被删除时，引用它的行也应该被自动删除

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

    您的 add_foreign_key 调用如下所示:

    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
    

    在这种情况下.您可能希望在模型中保留 dependent::delete_all 以提醒发生了什么，或者您可以给自己留下评论.

    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.

    这篇关于PG::ForeignKeyViolation:错误:更新或删除表“xxx"；违反外键约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！