Ruby-on-rails Rails 自动分配已存在的 id

Rails auto-assigning id that already exists

提问by D-Nice

I create a new record like so:

我创建了一个新记录，如下所示：

truck = Truck.create(:name=>name, :user_id=>2)

truck = Truck.create(:name=>name, :user_id=>2)

My database currently has several thousand entities for truck, but I assigned the id's to several of them, in a way that left some id's available. So what's happening is rails creates item with id = 150 and it works fine. But then it tries to create an item and assign it id = 151, but that id may already exist, so I'm seeing this error:

我的数据库目前有几千个卡车实体，但我将 ID 分配给了其中的几个，从而使一些 ID 可用。所以发生的事情是 rails 创建了 id = 150 的项目并且它工作正常。但随后它尝试创建一个项目并为其分配 id = 151，但该 id 可能已经存在，所以我看到了这个错误：

ActiveRecord::RecordNotUnique (PG::Error: ERROR: duplicate key value violates unique constraint "companies_pkey" DETAIL: Key (id)=(151) already exists.

ActiveRecord::RecordNotUnique (PG::Error: ERROR: duplicate key value violates unique constraint "companies_pkey" DETAIL: Key (id)=(151) already exists.

And the next time I run the action, it will simply assign the id 152, which will work fine if that value isn't already taken. How can I get rails to check whether an ID already exists before it assigns it?

下次我运行该操作时，它会简单地分配 id 152，如果尚未采用该值，这将正常工作。如何让 Rails 在分配 ID 之前检查它是否已经存在？

Thanks!

谢谢！

EDIT

编辑

The Truck id is what is being duplicated. The user already exists and is a constant in this case. It actually is a legacy issue that I have to deal with. One option, is to re-create the table at let rails auto assign every id this time around. I'm beginning to think this may be the best choice because I'm have a few other problems, but the migration for doing this would be very complicated because Truck is a foreign key in so many other tables. Would there be a simple way to have rails create a new table with the same data already stored under Truck, with auto-assigned ID's and maintaining all existing relationships?

卡车 ID 是复制的内容。用户已经存在并且在这种情况下是一个常量。这实际上是我必须处理的遗留问题。一种选择是重新创建表，让 rails 这次自动分配每个 id。我开始认为这可能是最好的选择，因为我还有其他一些问题，但是执行此操作的迁移将非常复杂，因为 Truck 是许多其他表中的外键。是否有一种简单的方法可以让 rails 创建一个新表，其中包含已存储在 Truck 下的相同数据、自动分配的 ID 并维护所有现有关系？

采纳答案by Dondi Michael Stroma

Rails is probably using the built-in PostgreSQL sequence. The idea of a sequence is that it is only used once.

Rails 可能正在使用内置的 PostgreSQL 序列。序列的想法是它只使用一次。

The simplest solution is to set the sequence for your company.id column to the highest value in the table with a query like this:

最简单的解决方案是使用如下查询将 company.id 列的序列设置为表中的最大值：

SELECT setval('company_id_seq', (SELECT max(id) FROM company));

I am guessing at your sequence name "company_id_seq", table name "company", and column name "id" ... please replace them with the correct ones. You can get the sequence name with SELECT pg_get_serial_sequence('tablename', 'columname');or look at the table definition with \d tablename.

我在猜测您的序列名称“company_id_seq”、表名称“company”和列名称“id”……请将它们替换为正确的。您可以使用 获取序列名称SELECT pg_get_serial_sequence('tablename', 'columname');或查看表定义\d tablename。

An alternate solution is to override the save() method in your company class to manually set the company id for new rows before saving.

另一种解决方案是覆盖公司类中的 save() 方法，以便在保存之前手动设置新行的公司 ID。

回答by Apie

I did this which solved the issue for me.

我这样做为我解决了这个问题。

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

I found the reset_pk_sequence! from this thread. http://www.ruby-forum.com/topic/64428

我找到了 reset_pk_sequence！从这个线程。http://www.ruby-forum.com/topic/64428

回答by inye

Based on @Apie answer.

基于@Apie回答。

You can make a task and run when you need with:

您可以使用以下命令创建任务并在需要时运行：

rake database:correction_seq_id

You create tasks like this:

你创建这样的任务：

rails g task database correction_seq_id

And in the file generated (lib/tasks/database.rake) put:

并在生成的文件 ( lib/tasks/database.rake) 中放置：

namespace :database do
    desc "Correction of sequences id"
    task correction_seq_id: :environment do
        ActiveRecord::Base.connection.tables.each do |t|
            ActiveRecord::Base.connection.reset_pk_sequence!(t)
        end
    end
end

回答by mynameiscoffey

Sounds to me like a database problem and not a Rails problem. Is it possible your database has an improper identity seed on your idcolumn? To test try doing a couple inserts directly into your database and see if the same behavior exists.

对我来说听起来像是数据库问题而不是 Rails 问题。您的数据库是否有可能在您的id列上有不正确的身份种子？要进行测试，请尝试直接在您的数据库中执行几次插入操作，看看是否存在相同的行为。

回答by Jigar Bhatt

I resolved this issue by following command.

我通过以下命令解决了这个问题。

Run this in your rails console

在 Rails 控制台中运行它

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