可以将文章内容翻译成中文,广告屏蔽插件可能会导致该功能失效(如失效,请关闭广告屏蔽插件后再试):
问题:
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.
回答1:
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
:
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
.
In 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:
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):
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
:
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.
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:
I have tried it in Rails 4.2. To add your custom primary key, you can write your migration as :
# 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.
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 :
Loading development environment (Rails 4.2.1)
=> Unable to load pry
>> Track.primary_key
=> \"apple_id\"
>>
回答5:
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.
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
回答6:
I am on Rails 2.3.5 and my following way works with 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.
回答7:
In Rails 5 you can do
create_table :employees, id: :string do |t|
t.string :first_name
t.string :last_name
end
See create_table documentation.
回答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
.
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 like
sandbox:
adapter: sqlite3
database: db/sandbox.sqlite3
pool: 5
timeout: 5000
in 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.
回答9:
I found a solution to this that works with 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:
self.primary_key = :emp_id
回答10:
The trick that worked for me on Rails 3 and MySQL was this:
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
Seems that MySQL converts the unique index on a non null column to a primary key!
回答11:
you have to use the option :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
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.
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!
回答14:
Adding index works for me, I\'m using MySql btw.
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