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