Rails core - Jul 2010 - 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.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com.
To unsubscribe from this group, send email to
rubyonrails-core+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.

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


On Jul 8, 2010, at 10:02 AM, Neeraj Singh wrote:
> 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.
> 
> -- 
> You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
> To post to this group, send email to rubyonrails-core@googlegroups.com.
> To unsubscribe from this group, send email to
rubyonrails-core+unsubscribe@googlegroups.com.
> For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.
> 
-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com.
To unsubscribe from this group, send email to
rubyonrails-core+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.

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|  }



On Jul 8, 12:15 pm, Ken Collins <k...@metaskills.net>
wrote:
> 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
>
> On Jul 8, 2010, at 10:02 AM, Neeraj Singh wrote:
>
>
>
> > 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.
>
> > --
> > You received this message because you are subscribed to the Google
Groups "Ruby on Rails: Core" group.
> > To post to this group, send email to
rubyonrails-core@googlegroups.com.
> > To unsubscribe from this group, send email to
rubyonrails-core+unsubscribe@googlegroups.com.
> > For more options, visit this group
athttp://groups.google.com/group/rubyonrails-core?hl=en.
-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com.
To unsubscribe from this group, send email to
rubyonrails-core+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.

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


On Jul 8, 2010, at 1:10 PM, Neeraj Singh wrote:
> 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|  }
> 
> 
> 
> On Jul 8, 12:15 pm, Ken Collins <k...@metaskills.net> wrote:
>> 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
>> 
>> On Jul 8, 2010, at 10:02 AM, Neeraj Singh wrote:
>> 
>> 
>> 
>>> 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.
>> 
>>> --
>>> You received this message because you are subscribed to the Google
Groups "Ruby on Rails: Core" group.
>>> To post to this group, send email to
rubyonrails-core@googlegroups.com.
>>> To unsubscribe from this group, send email to
rubyonrails-core+unsubscribe@googlegroups.com.
>>> For more options, visit this group
athttp://groups.google.com/group/rubyonrails-core?hl=en.
> 
> -- 
> You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
> To post to this group, send email to rubyonrails-core@googlegroups.com.
> To unsubscribe from this group, send email to
rubyonrails-core+unsubscribe@googlegroups.com.
> For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.
> 
-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com.
To unsubscribe from this group, send email to
rubyonrails-core+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.

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.

On Jul 8, 1:29 pm, Ken Collins <k...@metaskills.net>
wrote:
> 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
>
> On Jul 8, 2010, at 1:10 PM, Neeraj Singh wrote:
>
>
>
> > 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|  }
>
> > On Jul 8, 12:15 pm, Ken Collins <k...@metaskills.net> wrote:
> >> 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
>
> >> On Jul 8, 2010, at 10:02 AM, Neeraj Singh wrote:
>
> >>> 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.
>
> >>> --
> >>> You received this message because you are subscribed to the
Google Groups "Ruby on Rails: Core" group.
> >>> To post to this group, send email to
rubyonrails-core@googlegroups.com.
> >>> To unsubscribe from this group, send email to
rubyonrails-core+unsubscribe@googlegroups.com.
> >>> For more options, visit this group
athttp://groups.google.com/group/rubyonrails-core?hl=en.
>
> > --
> > You received this message because you are subscribed to the Google
Groups "Ruby on Rails: Core" group.
> > To post to this group, send email to
rubyonrails-core@googlegroups.com.
> > To unsubscribe from this group, send email to
rubyonrails-core+unsubscribe@googlegroups.com.
> > For more options, visit this group
athttp://groups.google.com/group/rubyonrails-core?hl=en.
-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com.
To unsubscribe from this group, send email to
rubyonrails-core+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.

On Jul 8, 2010, at 2:23 PM, Neeraj Singh wrote:
> 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.
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

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com.
To unsubscribe from this group, send email to
rubyonrails-core+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.