Creating non standard primary key a confusing and does not work with primary key of type 'string'

Case 1:

create_table :users, :id => false, :primary_key => :user_id do |t|
  t.integer :user_id
  t.string :name
  t.timestamps
end
Above migration produces following sql: CREATE TABLE
"users" ("user_id" integer, "name" varchar(255), "created_at"
datetime, "updated_at" datetime)
Exactly same sql would be generated if :primary_key option is not
passed. Since :primary_key option does not do anything user should be
warned that the newly created that does NOT have user_id as the
primary_key.

Case 2:
create_table :users, :primary_key => :user_id do |t|
  t.integer :user_id
  t.string :name
  t.timestamps
end
Above migration produces following sql: CREATE TABLE
"users" ("user_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "name"
varchar(255), "created_at" datetime, "updated_at" datetime)
Everything looks okay here.

Case 3:
create_table :users, :primary_key => :user_id do |t|
  t.string :user_id
  t.string :name
  t.timestamps
end
Above migration produces following sql: CREATE TABLE
"users" ("user_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "name"
varchar(255), "created_at" datetime, "updated_at" datetime)
Note that in the above case I have declared user_id to be string.
However the sql produced is setting "user_id" as integer in all three
adapters.

1) Related to Case 3: Why is rails setting the primary_key to be
integer in all three adapters even though I am asking that my
primary_key should be string?

3) Related to Case 1: I think user should not be allowed to
use :primary_key option if user is also passing :id => false. In case1
I was under the impression that database is treating user_id as the
primary key however the sql to generate table did not set user_id as
primary key.

Technically this would be a topic for the non-core list unless you had a patch or something. But...

If I look at the docs and I know the way the adapter's work, the table options gives the developer full control for all situations. The :primary_key option will always assume an integer. In the case where you are not OK with that, you are supposed to pass :id => false and then do all the leg work of making datatype that does what you want.

create_table :users, :id => false do |t|
  t.string :user_id
  t.string :name
  t.timestamps
end

That means the :primary_key option is only a connivence for naming and auto incrementing integer column per adapter.

- Ken

Hi Ken,

My intent is to fix the code. However before that I want to verify
that I am on the right path.

As I have shown in case if I am not happy with my primary key being
"id" and if I want my primary key to be "car_id" of type 'string'
there is NO way I can do that. The only way is to have my alter
statement which can be executed to set my string column 'user_id' as
primary_key.

Also if I say :primary_key => :user_id that should mean I want my
primary_key as 'user_id' and not 'id'. And in this case I should not
have to pass 'id > false'.

If you look at Case 2 you will notice that id => true is making
'user_id' auto-increment. This goes to suggest that :id => true/false
has taken on responsibilities of auto-increment which goes beyond the
name 'id'. 'id' seems like wrong key for the job.

I asked some of my friends working with me in office what the result
of following three statements will be. They all guessed wrong because
it not intuitive.

create_table :users, :id => false, :primary_key => :user_id { |t| }
create_table :users, :id => true, :primary_key => :user_id { |t| }
create_table :users, :primary_key => :user_id { |t| }

Neeraj,

Your missing my point. When you say "there is NO way I can do that", your are incorrect. There is no way you can do it in your examples because you are taking the :primary_key option to mean something else other than how the documentation specifies – a simple way to rename the auto generated auto increment integer data type. Simply do the :id => false option and you get what you need, a table that you can make the ID what you want, string or anything else. For instance here is one specific to SQL Server that creates a table with no auto generated [id] column and a custom SQL Server specific uniqueidentifier guid datatype.

create_table :users, :id => false do |t|
  t.string :name
  t.timestamps
end
execute %|ALTER TABLE [users] ADD [user_id] uniqueidentifier DEFAULT NEWID();|

class User < ActiveRecord::Base
  set_primary_key :user_id
end

At best this is a documentation issue, either not reading it or needing better examples to accommodate your edge case. Either way the schema statements will allow you to do all you want for the create table block.

- Ken

I see you point. And I agree.

However I still think that rails should take some of the
responsibility and should clarify to user following things.

1)
create_table :users, :id => false, :primary_key => :user_id do |t|
  t.integer :user_id
  t.string :name
  t.timestamps
end
Rails should throw an error rather than setting no column as primary
key.

2)
create_table :users, :primary_key => :user_id do |t|
  t.string :user_id
  t.string :name
  t.timestamps
end
Rails can clearly see that user is setting user_id column as string.
Still rails goes ahead and creates a column which is integer . That's
just wrong.

In the above two cases rails can just throw an error rather than doing
things in an opinionated way without any warning.

Thanks for your point of view.

Throwing errors in migrations for all but the most extreme errors is VERY BAD - several popular databases don't have transactional DDL, so the result of erroring out of a migration partway through can be a DB that can't go forward (errors / existing tables) and can't go back (tables missing for the down migrations).

I suspect that's why create_table does it's best to muddle through despite somewhat contradictory options.

--Matt Jones