使用 Rails，如何将主键设置为不是整数类型的列? [英] Using Rails, how can I set my primary key to not be an integer-typed column?

问题描述

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

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

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

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.

推荐答案

不幸的是，我已经确定不使用 execute 是不可能做到的.

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

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

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

在 因此我们可以看到，当您尝试在 create_table 选项中指定主键时，它会创建一个具有指定名称的主键(或者，如果没有指定，id).它通过调用您可以在表定义块中使用的相同方法来实现此目的:primary_key.

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.

在 schema_statements.rb:

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

这只是创建了一个指定名称类型为 :primary_key 的列.这在标准数据库适配器中设置如下:

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

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

解决方法

由于我们坚持使用这些作为主键类型，我们必须使用 execute 创建一个不是整数的主键(PostgreSQL 的 serial 是一个使用序列的整数):

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):

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);"

并且作为 Sean McCleary 提到，您的 ActiveRecord 模型应该使用 set_primary_key 设置主键:

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

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

这篇关于使用 Rails，如何将主键设置为不是整数类型的列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！