使用Rails，如何将主键设置为非整数类型的列?

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 employees where employees are identified by an alphanumeric string, e.g. "134SNW".

我正在使用Rails迁移来管理数据库模式，我正在创建一个简单的表，在这个表中我希望使用非整型值作为主键(特别是字符串)。为了摆脱我的问题，假设有一个表雇员，在这个表雇员通过一个字母数字字符串来识别，例如。“134 snw”。

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_id and 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 execute call?

它给我的是它完全忽略了直线t。string:emp_id，并将其设置为整数列。是否有其他方法可以让rails为我生成PRIMARY_KEY约束(我正在使用PostgreSQL)，而不必在执行调用中编写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.

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

14 个解决方案

#1

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_table options, 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:

这只创建一个列，指定类型为: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 execute to create a primary key that is not an integer (PostgreSQL's serial is an integer using a sequence):

由于我们一直把这些作为主键类型，所以我们必须使用execute创建一个非整数的主键(PostgreSQL的串行是使用序列的整数):

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

#2

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.

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

#3

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

#4

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为小部件类的主键，然后在创建对象时由您来填充字段。您应该能够使用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

#5

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。

#6

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 :

在查看列(名称、类型、选项={})的文档时，阅读一行:

The type parameter 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.

类型参数通常是迁移本机类型之一，它是以下类型之一:::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 :

我得到了上面的ide。下面是运行此迁移后的表元数据:

[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"
>>

#7

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文档。

#8

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.

我自己不是一个邮差忍者，我是从泽维尔·谢伊在《邮差》上出色的PeepCode视频中得到这个信息的。这段视频实际上忽略了亚伦·帕特森的一个图书馆，我想是Texticle但是我可能记错了。但除此之外，它非常棒。

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

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

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

in config/database.yml.

在config /形式。

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。

#9

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:

和员工。rb模型:

self.primary_key = :emp_id

#10

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:

所以:

1. use :id => false so as not to generate an integer primary key
2. 使用:id => false，以免生成整型主键
3. use the desired datatype, and add :null => false
4. 使用所需的数据类型，并添加:null => false
5. add a unique index on that column
6. 在该列上添加惟一索引

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

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

#11

you have to use the option :id => false

您必须使用选项:id => false

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

#12

How about this solution,

这个解决方案,

Inside Employee model why can't we add code that will check for uniqueness in coloumn, for ex: Assume Employee is Model in that you have EmpId which is string then for that we can add ":uniqueness => true" to EmpId

在Employee模型中，为什么我们不能添加一些代码来检查coloumn中的唯一性，例如:假设Employee是一个模型，因为你有一个EmpId，它是字符串，那么我们可以为EmpId添加":unique => true"

    class Employee < ActiveRecord::Base
      validates :EmpId , :uniqueness => true
    end

I am not sure that this is solution but this worked for me.

我不确定这是解决方案，但这对我起作用了。

#13

I know this is an old thread I stumbled across... but I'm kind of shocked no one mentioned DataMapper.

我知道这是我偶然发现的一条老线索……但我很震惊没有人提到DataMapper。

I find if you need to stray out of the ActiveRecord convention, I've found that it is a great alternative. Also its a better approach for legacy and you can support the database "as-is".

我发现如果您需要偏离ActiveRecord约定，我发现它是一个很好的选择。它也是一种更好的遗留方法，您可以支持数据库“按原样”。

Ruby Object Mapper (DataMapper 2) holds a lot of promise and build on AREL principles, too!

Ruby对象映射器(DataMapper 2)也有很多希望，并建立在AREL原则之上!

#14

Adding index works for me, I'm using MySql btw.

添加索引对我来说是可行的，顺便说一下，我用的是MySql。

create_table :cards, {:id => false} do |t|
    t.string :id, :limit => 36
    t.string :name
    t.string :details
    t.datetime :created_date
    t.datetime :modified_date
end
add_index :cards, :id, :unique => true