使用 Rails,如何将主键设置为不是整数类型列?
我正在使用 Rails 迁移来管理数据库模式,并且正在创建一个简单的表,我想在其中使用非整数值作为主键(特别是字符串)。 为了摆脱我的问题,假设有一个表employees
,其中员工由字母数字字符串标识,例如“134SNW”
。
我尝试在这样的迁移中创建表:
create_table :employees, {:primary_key => :emp_id} do |t|
t.string :emp_id
t.string :first_name
t.string :last_name
end
这给我的似乎是它完全忽略了行 t.string :emp_id 并继续将其设为整数列。 有没有其他方法可以让 Rails 为我生成 PRIMARY_KEY 约束(我正在使用 PostgreSQL),而不必在 execute
调用中编写 SQL?
注意:我知道使用字符串列作为主键并不是最好的选择,所以请不要只说添加整数主键。 无论如何我可以添加一个,但这个问题仍然有效。
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"
.
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?
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.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(15)
不幸的是,我确定不使用
execute
就不可能做到这一点。为什么它不起作用
通过检查ActiveRecord源代码,我们可以找到
create_table
的代码:在
schema_statements.rb
:因此我们可以看到,当您尝试在中指定主键时
create_table
选项,它会创建一个具有指定名称的主键(或者,如果未指定,则创建一个id
)。 它通过调用可在表定义块中使用的相同方法来实现此目的:primary_key
。在
schema_statements.rb
:这只是创建一个具有指定名称的类型
:primary_key
的列。 在标准数据库适配器中,它被设置为以下内容:解决方法
由于我们坚持将这些作为主键类型,因此我们必须使用
execute
创建一个不是整数的主键( PostgreSQL 的serial
是使用序列的整数):如 Sean McCleary 提到,您的 ActiveRecord 模型应使用
set_primary_key
设置主键:Unfortunately, I've determined it's not possible to do it without using
execute
.Why it doesn't work
By examining the ActiveRecord source, we can find the code for
create_table
:In
schema_statements.rb
: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
.In
schema_statements.rb
:This just creates a column with the specified name of type
:primary_key
. This is set to the following in the standard database adapters: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'sserial
is an integer using a sequence):And as Sean McCleary mentioned, your ActiveRecord model should set the primary key using
set_primary_key
:这有效:
它可能不太漂亮,但最终结果正是您想要的。
This works:
It may not be pretty, but the end result is exactly what you want.
我有一种方法来处理这个问题。 执行的 SQL 是 ANSI SQL,因此它可能适用于大多数符合 ANSI SQL 的关系数据库。 我已经测试过这适用于 MySQL。
迁移:
在您的模型中执行以下操作:
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.
Migration:
In your model do this:
在 Rails 5 中,您可以
参阅 create_table 文档 。
In Rails 5 you can do
See create_table documentation.
我在 Rails 4.2 中尝试过。 要添加自定义主键,您可以将迁移编写为:
在查看
column(name ,输入 options = {})
并阅读以下行:我得到了上面的 IDE,正如我所展示的那样。 这是运行此迁移后的表元数据:
来自 Rails 控制台:
I have tried it in Rails 4.2. To add your custom primary key, you can write your migration as :
While looking at the documentation of
column(name, type, options = {})
and read the line :I got the above ides as i have shown. Here is the table meta data after running this migration :
And from Rails console :
看起来可以使用这种方法:
这将使列 widget_id 成为 Widget 类的主键,然后由您在创建对象时填充该字段。 您应该能够使用 before create 回调来执行此操作。
所以类似的事情
It looks like it is possible to do using this approach:
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.
So something along the lines of
我在 Rails 2.3.5 上,我的以下方式适用于 SQLite3
不需要 :id =>; 错误的。
I am on Rails 2.3.5 and my following way works with SQLite3
There is no need for :id => false.
在几乎每个解决方案都说“这对我在 X 数据库上有用”之后,我看到原始发布者的评论,大意是“对我在 Postgres 上不起作用”。 这里真正的问题实际上可能是 Rails 中的 Postgres 支持,它并不是完美无缺的,而且在 2009 年这个问题最初发布时可能更糟。 例如,如果我没记错的话,如果你使用 Postgres,你基本上无法从 rake db:schema:dump 获得有用的输出。
我自己不是 Postgres 忍者,我从 Xavier Shay 在 Postgres 上精彩的 PeepCode 视频中获得了此信息。 该视频实际上俯瞰了 Aaron Patterson 的图书馆,我认为是 Texticle,但我可能记错了。 但除此之外它非常棒。
无论如何,如果您在 Postgres 上遇到此问题,请查看该解决方案是否适用于其他数据库。 也许使用 Rails new 生成一个新应用程序作为沙箱,或者只是创建类似于
config/database.yml 的内容。
如果您可以验证这是一个 Postgres 支持问题,并且您找到了解决方案,请向 Rails 贡献补丁或将您的修复程序打包到 gem 中,因为 Rails 社区中的 Postgres 用户群非常庞大,这主要归功于 Heroku 。
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
.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.
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 likein
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.
我找到了一个适用于 Rails 3 的解决方案:
迁移文件:
在 employee.rb 模型中:
I found a solution to this that works with Rails 3:
The migration file:
And in the employee.rb model:
在 Rails 3 和 MySQL 上对我有用的技巧是这样的:
所以:
似乎 MySQL 将非空列上的唯一索引转换为主键!
The trick that worked for me on Rails 3 and MySQL was this:
So:
Seems that MySQL converts the unique index on a non null column to a primary key!
你必须使用选项 :id => 错误的
you have to use the option :id => false
这个解决方案怎么样,
在 Employee 模型内部,为什么我们不能添加代码来检查列中的唯一性,例如:假设 Employee 是模型,因为您有 EmpId,它是字符串,那么我们可以添加 ":uniqueness => true" 到 EmpId
我不确定这是否是解决方案,但这对我有用。
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
I am not sure that this is solution but this worked for me.
我知道这是我偶然发现的一个旧线程...但我有点震惊没有人提到 DataMapper。
我发现如果您需要偏离 ActiveRecord 约定,我发现它是一个很好的选择。 这也是一种更好的遗留方法,您可以“按原样”支持数据库。
Ruby 对象映射器 (DataMapper 2) 拥有很多前景,并且也建立在 AREL 原则之上!
I know this is an old thread I stumbled across... but I'm kind of shocked no one mentioned 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".
Ruby Object Mapper (DataMapper 2) holds a lot of promise and build on AREL principles, too!
添加索引对我有用,我正在使用 MySql 顺便说一句。
Adding index works for me, I'm using MySql btw.
当前的 Rails 7(从 Rails 5 开始?)允许开箱即用(请参阅 create_table 的 RDoc):
要引用此字符串主键,您的迁移如下所示
Current Rails 7 (starting with Rails 5?) allows this out of the box (see RDoc of create_table):
To reference this string primary key your migration looks like