Ruby-on-rails 使用 Rails，如何将主键设置为不是整数类型的列？

Question

提问by Rudd Zwolinski

I'm using Rails migrations to manage a database schema, and I'm creating a simple table where I'd like to use a non-integer value as the primary key (in particular, a string). To abstract away from my problem, let's say there's a table employeeswhere employees are identified by an alphanumeric string, e.g. "134SNW".

我正在使用 Rails 迁移来管理数据库模式，并且我正在创建一个简单的表，我想在其中使用非整数值作为主键（特别是字符串）。为了摆脱我的问题，假设有一个表employees，其中员工由字母数字字符串标识，例如"134SNW".

I've tried creating the table in a migration like this:

我试过在这样的迁移中创建表：

create_table :employees, {:primary_key => :emp_id} do |t|
    t.string :emp_id
    t.string :first_name
    t.string :last_name
end

What this gives me is what seems like it completely ignored the line t.string :emp_idand went ahead and made it an integer column. Is there some other way to have rails generate the PRIMARY_KEY constraint (I'm using PostgreSQL) for me, without having to write the SQL in an executecall?

这给我的是它似乎完全忽略了该行t.string :emp_id并继续使其成为整数列。有没有其他方法可以让 rails 为我生成 PRIMARY_KEY 约束（我使用的是 PostgreSQL），而不必在execute调用中编写 SQL ？

NOTE: I know it's not best to use string columns as primary keys, so please no answers just saying to add an integer primary key. I may add one anyway, but this question is still valid.

注意：我知道最好不要使用字符串列作为主键，所以请不要回答只是说添加一个整数主键。无论如何我可以添加一个，但这个问题仍然有效。

Answer 1

回答by Rudd Zwolinski

Unfortunately, I've determined it's not possible to do it without using execute.

不幸的是，我已经确定不使用execute.

Why it doesn't work

为什么它不起作用

By examining the ActiveRecord source, we can find the code for create_table:

通过检查 ActiveRecord 源代码，我们可以找到以下代码create_table：

In schema_statements.rb:

在schema_statements.rb：

def create_table(table_name, options={})
  ...
  table_definition.primary_key(options[:primary_key] || Base.get_primary_key(table_name.to_s.singularize)) unless options[:id] == false
  ...
end

So we can see that when you try to specify a primary key in the create_tableoptions, it creates a primary key with that specified name (or, if none is specified, id). It does this by calling the same method you can use inside a table definition block: primary_key.

所以我们可以看到，当您尝试在create_table选项中指定主键时，它会创建一个具有指定名称的主键（或者，如果没有指定，则为id）。它通过调用您可以将表的定义块中使用相同的方法：primary_key。

In schema_statements.rb:

在schema_statements.rb：

def primary_key(name)
  column(name, :primary_key)
end

This just creates a column with the specified name of type :primary_key. This is set to the following in the standard database adapters:

这只是创建一个具有指定名称 type 的列:primary_key。这在标准数据库适配器中设置如下：

PostgreSQL: "serial primary key"
MySQL: "int(11) DEFAULT NULL auto_increment PRIMARY KEY"
SQLite: "INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL"

The workaround

解决方法

Since we're stuck with these as the primary key types, we have to use executeto create a primary key that is not an integer (PostgreSQL's serialis an integer using a sequence):

由于我们坚持使用这些作为主键类型，我们必须使用execute创建一个不是整数的主键（PostgreSQLserial是使用序列的整数）：

create_table :employees, {:id => false} do |t|
  t.string :emp_id
  t.string :first_name
  t.string :last_name
end
execute "ALTER TABLE employees ADD PRIMARY KEY (emp_id);"

And as Sean McCleary mentioned, your ActiveRecord model should set the primary key using set_primary_key:

正如Sean McCleary 提到的，您的 ActiveRecord 模型应该使用set_primary_key以下方法设置主键：

class Employee < ActiveRecord::Base
  set_primary_key :emp_id
  ...
end

Answer 2

回答by Austin

This works:

这有效：

create_table :employees, :primary_key => :emp_id do |t|
  t.string :first_name
  t.string :last_name
end
change_column :employees, :emp_id, :string

It may not be pretty, but the end result is exactly what you want.

它可能不漂亮，但最终结果正是您想要的。

Answer 3

回答by Sean McCleary

I have one way of handling this. The executed SQL is ANSI SQL so it will likely work on most ANSI SQL compliant relational databases. I have tested that this works for MySQL.

我有一种处理方法。执行的 SQL 是 ANSI SQL，因此它可能适用于大多数符合 ANSI SQL 的关系数据库。我已经测试过这适用于 MySQL。

Migration:

移民：

create_table :users, :id => false do |t|
    t.string :oid, :limit => 10, :null => false
    ...
end
execute "ALTER TABLE users ADD PRIMARY KEY (oid);"

In your model do this:

在您的模型中执行以下操作：

class User < ActiveRecord::Base
    set_primary_key :oid
    ...
end

Answer 4

回答by Arup Rakshit

I have tried it in Rails 4.2. To add your custom primary key, you can write your migration as :

我在 Rails 4.2 中尝试过。要添加自定义主键，您可以将迁移编写为：

# tracks_ migration
class CreateTracks < ActiveRecord::Migration
  def change
    create_table :tracks, :id => false do |t|
      t.primary_key :apple_id, :string, limit: 8
      t.string :artist
      t.string :label
      t.string :isrc
      t.string :vendor_id
      t.string :vendor_offer_code

      t.timestamps null: false
    end
    add_index :tracks, :label
  end
end

While looking at the documentation of column(name, type, options = {})and read the line :

在查看文档column(name, type, options = {})并阅读以下内容时：

The typeparameter is normally one of the migrations native types, which is one of the following: :primary_key, :string, :text, :integer, :float, :decimal, :datetime, :time, :date, :binary, :boolean.

该type参数通常是迁移本机类型之一，它是以下之一： :primary_key, :string, :text, :integer, :float, :decimal, :datetime, :time, :date, :binary, :boolean .

I got the above ides as i have shown. Here is the table meta data after running this migration :

正如我所展示的，我得到了上述想法。这是运行此迁移后的表元数据：

[arup@music_track (master)]$ rails db
psql (9.2.7)
Type "help" for help.

music_track_development=# \d tracks
                    Table "public.tracks"
      Column       |            Type             | Modifiers
-------------------+-----------------------------+-----------
 apple_id          | character varying(8)        | not null
 artist            | character varying           |
 label             | character varying           |
 isrc              | character varying           |
 vendor_id         | character varying           |
 vendor_offer_code | character varying           |
 created_at        | timestamp without time zone | not null
 updated_at        | timestamp without time zone | not null
 title             | character varying           |
Indexes:
    "tracks_pkey" PRIMARY KEY, btree (apple_id)
    "index_tracks_on_label" btree (label)

music_track_development=#

And from Rails console :

从 Rails 控制台：

Loading development environment (Rails 4.2.1)
=> Unable to load pry
>> Track.primary_key
=> "apple_id"
>>

Answer 5

回答by Pavel Chuchuva

In Rails 5 you can do

在 Rails 5 中你可以做

create_table :employees, id: :string do |t|
  t.string :first_name
  t.string :last_name
end

See create_table documentation.

请参阅create_table 文档。

Answer 6

回答by paulthenerd

It looks like it is possible to do using this approach:

看起来可以使用这种方法：

create_table :widgets, :id => false do |t|
  t.string :widget_id, :limit => 20, :primary => true

  # other column definitions
end

class Widget < ActiveRecord::Base
  set_primary_key "widget_id"
end

That will make the column widget_id the primary key for the Widget class, then it is up to you to populate the field when objects are created. You should be able to do so using the before create callback.

这将使widget_id 列成为Widget 类的主键，然后由您在创建对象时填充该字段。您应该能够使用 before create 回调来做到这一点。

So something along the lines of

所以类似的东西

class Widget < ActiveRecord::Base
  set_primary_key "widget_id"

  before_create :init_widget_id

  private
  def init_widget_id
    self.widget_id = generate_widget_id
    # generate_widget_id represents whatever logic you are using to generate a unique id
  end
end

Answer 7

回答by Trung LE

I am on Rails 2.3.5 and my following way works with SQLite3

我在 Rails 2.3.5 上，我的以下方式适用于 SQLite3

create_table :widgets, { :primary_key => :widget_id } do |t|
  t.string :widget_id

  # other column definitions
end

There is no need for :id => false.

不需要:id => false。

Answer 8

回答by Giles Bowkett

After nearly every solution which says "this worked for me on X database", I see a comment by the original poster to the effect of "didn't work for me on Postgres." The real issue here may in fact be the Postgres support in Rails, which is not flawless, and was probably worse back in 2009 when this question originally posted. For instance, if I remember correctly, if you're on Postgres, you basically can't get useful output from rake db:schema:dump.

在几乎所有说“这对我在 X 数据库上有用”的解决方案之后，我看到原始海报的评论说“在 Postgres 上对我不起作用”。这里真正的问题实际上可能是 Rails 中的 Postgres 支持，这并非完美无缺，而且在 2009 年最初发布此问题时可能更糟。例如，如果我没记错的话，如果您使用 Postgres，您基本上无法从rake db:schema:dump.

I am not a Postgres ninja myself, I got this info from Xavier Shay's excellent PeepCode video on Postgres. That video actually overlooks a library by Aaron Patterson, I think Texticle but I could be remembering wrong. But other than that it's pretty great.

我本人不是 Postgres 忍者，我从 Xavier Shay 在 Postgres 上的优秀 PeepCode 视频中获得了这些信息。该视频实际上忽略了 Aaron Patterson 的图书馆，我认为 Texticle 但我可能记错了。但除此之外，它非常棒。

Anyway, if you're running into this problem on Postgres, see if the solutions work in other databases. Maybe use rails newto generate a new app as a sandbox, or just create something like

无论如何，如果您在 Postgres 上遇到此问题，请查看解决方案是否适用于其他数据库。也许用于rails new生成一个新的应用程序作为沙箱，或者只是创建类似的东西

sandbox:
  adapter: sqlite3
  database: db/sandbox.sqlite3
  pool: 5
  timeout: 5000

in config/database.yml.

在config/database.yml。

And if you can verify that it is a Postgres support issue, and you figure out a fix, please contribute patches to Rails or package your fixes in a gem, because the Postgres user base within the Rails community is pretty large, mainly thanks to Heroku.

如果您可以验证这是 Postgres 支持问题，并且您找到了修复程序，请为 Rails 贡献补丁或将您的修复程序打包到 gem 中，因为 Rails 社区中的 Postgres 用户群非常大，这主要归功于 Heroku .

Answer 9

回答by shicholas

I found a solution to this that works with Rails 3:

我找到了一个适用于 Rails 3 的解决方案：

The migration file:

迁移文件：

create_table :employees, {:primary_key => :emp_id} do |t|
  t.string :emp_id
  t.string :first_name
  t.string :last_name
end

And in the employee.rb model:

在employee.rb 模型中：

self.primary_key = :emp_id

Answer 10

回答by Clark

The trick that worked for me on Rails 3 and MySQL was this:

在 Rails 3 和 MySQL 上对我有用的技巧是：

create_table :events, {:id => false} do |t|
  t.string :id, :null => false
end

add_index :events, :id, :unique => true

So:

所以：

use :id => false so as not to generate an integer primary key
use the desired datatype, and add :null => false
add a unique index on that column

使用 :id => false 以免生成整数主键
使用所需的数据类型，并添加 :null => false
在该列上添加唯一索引

Seems that MySQL converts the unique index on a non null column to a primary key!

似乎 MySQL 将非空列上的唯一索引转换为主键！

Ruby-on-rails 使用 Rails，如何将主键设置为不是整数类型的列？

提问by Rudd Zwolinski

回答by Rudd Zwolinski

Why it doesn't work

为什么它不起作用

The workaround

解决方法

回答by Austin

回答by Sean McCleary

回答by Arup Rakshit

回答by Pavel Chuchuva

回答by paulthenerd

回答by Trung LE

回答by Giles Bowkett

回答by shicholas

回答by Clark

相关推荐

最近更新

标签

Ruby-on-rails 使用 Rails，如何将主键设置为不是整数类型的列？

提问by Rudd Zwolinski

回答by Rudd Zwolinski

Why it doesn't work

为什么它不起作用

The workaround

解决方法

回答by Austin

回答by Sean McCleary

回答by Arup Rakshit

回答by Pavel Chuchuva

回答by paulthenerd

回答by Trung LE

回答by Giles Bowkett

回答by shicholas

回答by Clark

相关推荐

Ruby-on-rails 在 rails 中使用 RSpec 和 Capybara 时未定义的方法“访问”

如何使用 ruby​​ 将 base64 字符串保存为图像

Ruby-on-rails 如何使用 link_to 添加 bootstrap modal 以便链接内容在 modal 中打开？

Ruby-on-rails 带有别名的 Rails has_many

相关推荐

最近更新

标签

如何使用 ruby 将 base64 字符串保存为图像