时间戳

首页 > 解决方案 > PG::ForeignKeyViolation：错误：在表“...”上插入或更新违反外键约束

ruby-on-rails - PG::ForeignKeyViolation：错误：在表“...”上插入或更新违反外键约束

问题描述

轨道上的红宝石

以下正在开发mySQL

但是在生产 PG（部署到 Heroku、postgreSQL）上引发错误并且仅针对某些记录！

我有的：

当我尝试触发下面的命令时，它可以正常工作 6 次

Car.create(reg_no: "001", group_id: 1)
Car.create(reg_no: "002", group_id: 2)
...

但

Car.create(reg_no: "007", group_id: 7)

其他人最终得到：

PG::ForeignKeyViolation: ERROR:  insert or update on table "cars" violates foreign key constraint "fk_rails_fa6b5abc5a"
DETAIL:  Key (group_id)=(7) is not present in table "sizes".

所以不接受 6 之后的所有 group_id。我不明白是什么让他们与众不同。

Group_id 7 由 Size: M 和 Grade: New 组成，两者都存在于相应的表中。

irb(main):001:0>Group.find(7)
D, [2018-06-07T14:34:34.802158 #4] DEBUG -- :   Group Load (1.3ms)  SELECT  "groups".* FROM "groups" WHERE "groups"."id" = $1 LIMIT $2  [["id", 7], ["LIMIT", 1]]
=> #<Group id: 7, size_id: 3, grade_id: 1, created_at: "2018-06-06 18:07:26", updated_at: "2018-06-06 18:07:26">
irb(main):002:0> Size.find(3)
D, [2018-06-07T14:35:59.133721 #4] DEBUG -- :   Size Load (1.3ms)  SELECT  "sizes".* FROM "sizes" WHERE "sizes"."id" = $1 LIMIT $2  [["id", 3], ["LIMIT", 1]]
=> #<Size id: 3, name: "M", created_at: "2018-06-06 18:07:26", updated_at: "2018-06-06 18:07:26">
irb(main):004:0> Grade.find(1)
D, [2018-06-07T14:36:30.558192 #4] DEBUG -- :   Grade Load (1.4ms)  SELECT  "grades".* FROM "grades" WHERE "grades"."id" = $1 LIMIT $2  [["id", 1], ["LIMIT", 1]]
=> #<Grade id: 1, name: "New", created_at: "2018-06-06 18:07:26", updated_at: "2018-06-06 18:07:26">

不引发错误的第 6 组的同一组请求

irb(main):005:0> Group.find(6)
D, [2018-06-07T15:00:27.431814 #4] DEBUG -- :   Group Load (1.5ms)  SELECT  "groups".* FROM "groups" WHERE "groups"."id" = $1 LIMIT $2  [["id", 6], ["LIMIT", 1]]
=> #<Group id: 6, size_id: 2, grade_id: 3, created_at: "2018-06-06 18:07:26", updated_at: "2018-06-06 18:07:26">
irb(main):006:0> Size.find(2)
D, [2018-06-07T15:00:40.946858 #4] DEBUG -- :   Size Load (1.4ms)  SELECT  "sizes".* FROM "sizes" WHERE "sizes"."id" = $1 LIMIT $2  [["id", 2], ["LIMIT", 1]]
=> #<Size id: 2, name: "SM", created_at: "2018-06-06 18:07:26", updated_at: "2018-06-06 18:07:26">
irb(main):007:0> Grade.find(3)
D, [2018-06-07T15:00:49.220472 #4] DEBUG -- :   Grade Load (1.4ms)  SELECT  "grades".* FROM "grades" WHERE "grades"."id" = $1 LIMIT $2  [["id", 3], ["LIMIT", 1]]
=> #<Grade id: 3, name: "Old", created_at: "2018-06-06 18:07:26", updated_at: "2018-06-06 18:07:26">

楷模

class Car < ApplicationRecord
  belongs_to :group
  validates :reg_no, presence: true, length: { maximum: 6 },
                     uniqueness: { case_sensitive: false }
end

class Group < ApplicationRecord
  belongs_to :size
  belongs_to :grade
  has_many :cars, dependent: :destroy
  validates :size_id, presence: true
  validates :grade_id, presence: true
end

class Size < ApplicationRecord
  has_many :groups, dependent: :destroy
  has_many :grades, through: :groups
  validates :name, uniqueness: true
end

class Grade < ApplicationRecord
  has_many :groups, dependent: :destroy
  has_many :sizes, through: :groups
end

架构

create_table "cars", force: :cascade do |t|
    t.string "reg_no"
    t.integer "group_id"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.index ["group_id"], name: "index_cars_on_group_id"
    t.index ["reg_no"], name: "index_cars_on_reg_no", unique: true
  end

  create_table "groups", force: :cascade do |t|
    t.integer "size_id"
    t.integer "grade_id"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.index ["grade_id"], name: "index_groups_on_grade_id"
    t.index ["size_id", "grade_id"], name: "index_groups_on_size_id_and_grade_id", unique: true
    t.index ["size_id"], name: "index_groups_on_size_id"
  end

  create_table "sizes", force: :cascade do |t|
    t.string "name"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end

  create_table "grades", force: :cascade do |t|
    t.string "name"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end

这是来自服务器日志的更多跟踪详细信息，最后一次成功创建并且首先引发错误：

D, [2018-06-07T15:21:00.464588 #4] DEBUG -- :    (1.8ms)  COMMIT
D, [2018-06-07T15:21:00.466149 #4] DEBUG -- :   Group Load (1.1ms)  SELECT  "groups".* FROM "groups" WHERE "groups"."size_id" = $1 AND "groups"."grade_id" = $2 LIMIT $3  [["size_id", 2], ["grade_id", 1], ["LIMIT", 1]]
D, [2018-06-07T15:21:00.467749 #4] DEBUG -- :    (1.0ms)  BEGIN
D, [2018-06-07T15:21:00.469748 #4] DEBUG -- :   Group Load (1.1ms)  SELECT  "groups".* FROM "groups" WHERE "groups"."id" = $1 LIMIT $2  [["id", 4], ["LIMIT", 1]]
D, [2018-06-07T15:21:00.472005 #4] DEBUG -- :   Car Exists (1.2ms)  SELECT  1 AS one FROM "cars" WHERE LOWER("cars"."reg_no") = LOWER($1) LIMIT $2  [["reg_no", "ขก685"], ["LIMIT", 1]]
D, [2018-06-07T15:21:00.474472 #4] DEBUG -- :   Car Create (1.4ms)  INSERT INTO "cars" ("reg_no", "group_id", "created_at", "updated_at") VALUES ($1, $2, $3, $4) RETURNING "id"  [["reg_no", "ขก685"], ["group_id", 4], ["created_at", "2018-06-07 15:21:00.472251"], ["updated_at", "2018-06-07 15:21:00.472251"]]
D, [2018-06-07T15:21:00.476690 #4] DEBUG -- :    (1.8ms)  COMMIT

D, [2018-06-07T15:21:00.478864 #4] DEBUG -- :   Group Load (1.7ms)  SELECT  "groups".* FROM "groups" WHERE "groups"."size_id" = $1 AND "groups"."grade_id" = $2 LIMIT $3  [["size_id", 3], ["grade_id", 1], ["LIMIT", 1]]
D, [2018-06-07T15:21:00.480511 #4] DEBUG -- :    (1.0ms)  BEGIN
D, [2018-06-07T15:21:00.482638 #4] DEBUG -- :   Group Load (1.2ms)  SELECT  "groups".* FROM "groups" WHERE "groups"."id" = $1 LIMIT $2  [["id", 7], ["LIMIT", 1]]
D, [2018-06-07T15:21:00.485111 #4] DEBUG -- :   Car Exists (1.4ms)  SELECT  1 AS one FROM "cars" WHERE LOWER("cars"."reg_no") = LOWER($1) LIMIT $2  [["reg_no", "ขก681"], ["LIMIT", 1]]
D, [2018-06-07T15:21:00.488164 #4] DEBUG -- :   Car Create (1.9ms)  INSERT INTO "cars" ("reg_no", "group_id", "created_at", "updated_at") VALUES ($1, $2, $3, $4) RETURNING "id"  [["reg_no", "ขก681"], ["group_id", 7], ["created_at", "2018-06-07 15:21:00.485387"], ["updated_at", "2018-06-07 15:21:00.485387"]]
D, [2018-06-07T15:21:00.489452 #4] DEBUG -- :    (1.1ms)  ROLLBACK
rails aborted!
ActiveRecord::InvalidForeignKey: PG::ForeignKeyViolation: ERROR:  insert or update on table "cars" violates foreign key constraint "fk_rails_fa6b5abc5a"
DETAIL:  Key (group_id)=(7) is not present in table "sizes".
: INSERT INTO "cars" ("reg_no", "group_id", "created_at", "updated_at") VALUES ($1, $2, $3, $4) RETURNING "id"
/app/vendor/bundle/ruby/2.5.0/gems/activerecord-5.2.0/lib/active_record/connection_adapters/postgresql_adapter.rb:603:in `async_exec'
...

标签： ruby-on-railspostgresqlpg

解决方案

我们遇到了同样的问题。

您需要检查您的 schema.rb 并查看按钮。

您将看到您之前使用 foreign_key 创建的迁移，您必须再次更新它并运行迁移。

推荐阅读