postgresql 为什么 PG::UniqueViolation：错误：重复键值违反唯一约束？

why PG::UniqueViolation: ERROR: duplicate key value violates unique constraint?

提问by Orsay

I have a model Postand each time a post is created I want a new instance of Moderationto be created at the same time.

我有一个模型Post，每次创建帖子时，我都希望同时创建一个新实例Moderation。

So in post.rb I use the callback after_save :create_moderationThen write a private method :

所以在 post.rb 我使用回调after_save :create_moderation然后写一个私有方法：

 ...
 include Reportable
 after_save :create_moderation

 private
 def create_moderation
    self.create_moderation!(blog: Blog.first)
 end

But when a proposal is created I get this error :

但是当创建提案时，我收到此错误：

PG::UniqueViolation: ERROR: duplicate key value violates unique constraint "moderations_reportable" DETAIL: Key (reportable_type, reportable_id)=(Post, 25) already exists. : INSERT INTO "moderations" ("blog_id", "reportable_type", "reportable_id", "created_at", "updated_at", "blog_type") VALUES ($1, $2, $3, $4, $5, $6) RETURNING "id"

PG::UniqueViolation：错误：重复键值违反唯一约束“moderations_reportable”详细信息：键（reportable_type，reportable_id）=（Post，25）已经存在。: INSERT INTO "moderations" ("blog_id", "reportable_type", "reportable_id", "created_at", "updated_at", "blog_type") VALUES ($1, $2, $3, $4, $5, $6) RETURNING "id"

In reportable.rb I have :

在reportable.rb我有：

  has_one :moderation, as: :reportable, foreign_key: "reportable_id", foreign_type: "reportable_type", class_name: "Moderation"

Then few other methods for reportable object.

然后很少有其他方法用于可报告对象。

Note that this issue doesn't happen when I run the create method in console.

请注意，当我在控制台中运行 create 方法时不会发生此问题。

EDIT

编辑

  create_table "moderations", id: :serial, force: :cascade do |t|
    t.string "reportable_type", null: false
    t.string "reportable_id", null: false
    t.integer "blog_id", null: false
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.string "blog_type", null: false
    t.string "upstream_moderation", default: "unmoderate"
    t.index ["blog_id", "blog_type"], name: "moderations_blog"
    t.index ["reportable_type", "reportable_id"], name: "moderations_reportable", unique: true
  end



create_table "posts", id: :serial, force: :cascade do |t|
    t.text "title", null: false
    t.text "body", null: false
    t.integer "feature_id", null: false
    t.integer "author_id"
    t.integer "scope_id"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.integer "post_votes_count", default: 0, null: false
    t.index ["body"], name: "post_body_search"
    t.index ["created_at"], name: "index_posts_on_created_at"
    t.index ["author_id"], name: "index_posts_on_author_id"
    t.index ["feature_id"], name: "index_posts_on_feature_id"
    t.index ["proposal_votes_count"], name: "index_posts_on_post_votes_count"
    t.index ["title"], name: "post_title_search"
  end

采纳答案by seancdavis

Looks like you've added a unique index to your database:

看起来您已向数据库添加了唯一索引：

t.index ["reportable_type", "reportable_id"], name: "moderations_reportable", unique: true

With a unique index you will only be able to have one record with the same reportable_typeand reportable_id. It's likely that you're trying to create a moderation for a reportable that already has a moderation.

使用唯一索引，您将只能拥有一个具有相同reportable_type和 的记录reportable_id。您很可能正在尝试为已经进行审核的可报告内容创建审核。

回答by aashish

To fix the issue, we have to tell ActiveRecord to look at the sequence of the table:

为了解决这个问题，我们必须告诉 ActiveRecord 查看表的顺序：

ActiveRecord::Base.connection.reset_pk_sequence!('table_name')

Now ActiveRecord should have the correct sequence value, and should be able to assign new id's properly.

现在 ActiveRecord 应该有正确的序列值，并且应该能够正确分配新的 id。

To resolve error

解决错误

PG::UniqueViolation: ERROR: duplicate key value violates unique constraint "moderations_reportable" DETAIL: Key (reportable_type, reportable_id)=(Post, 25) already exists. : INSERT INTO "moderations" ("blog_id", "reportable_type", "reportable_id", "created_at", "updated_at", "blog_type") VALUES ($1, $2, $3, $4, $5, $6) RETURNING "id"

PG::UniqueViolation：错误：重复键值违反唯一约束“moderations_reportable”详细信息：键（reportable_type，reportable_id）=（Post，25）已经存在。: INSERT INTO "moderations" ("blog_id", "reportable_type", "reportable_id", "created_at", "updated_at", "blog_type") VALUES ($1, $2, $3, $4, $5, $6) RETURNING "id"

As error occurred on 'moderations' table.

由于在“审核”表上发生错误。

Run the following from rails consolefix

从rails 控制台修复运行以下命令

ActiveRecord::Base.connection.reset_pk_sequence!('moderations')

Thank you

谢谢

回答by Sergio Belevskij

Fix pkey sequences for all database:

修复所有数据库的 pkey 序列：

ActiveRecord::Base.connection.tables.each do |table_name| 
  ActiveRecord::Base.connection.reset_pk_sequence!(table_name)
end