Forum: Ruby on Rails non integer primary key

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
migo (Guest)
on 2007-07-03 13:07
(Received via mailing list)
Hallo,

I need to define a non integer primary key in the migrations an I am
kind of stuck here. All I seem to be able to do is either omit the
creation of the key by setting :id => false, or choose a differnet key
column by setting :primary_key => 'xxx'. Then a different primary key
with an integer datatype is created.

How do I force the schema definition to create a string primary key.
And before you ask, No I cannot change my data structure, it is
imported data, that will update itself every once in a while!

The primary key will be referenced via a foreign key from a different
table. I know I can set the 'set_primary_key "xxx" ' in the model
class, but mysql complains if there is a table without primary key.

Thanks for your help.
migo
Jon G. (Guest)
on 2007-07-03 18:42
(Received via mailing list)
migo wrote:
> imported data, that will update itself every once in a while!
>
> The primary key will be referenced via a foreign key from a different
> table. I know I can set the 'set_primary_key "xxx" ' in the model
> class, but mysql complains if there is a table without primary key.
>
> Thanks for your help.
> migo
>
>
Rails is very opinionated about certain things, and primary keys on the
tables is one of them.

In your case, I would consider omitting the primary key, and putting
these non-integer keys into a field called 'alphacode' or something
descriptive, but not 'id' (to prevent confusion with typical Rails id
fields in the future).  Then, whenever you want to lookup one of these
records, do Thing.find_by_alphacode('XYZ') isntead of Thing.find(1).
You can even consider overriding 'find' in the model to make this
seamless, allowing Thing.find('XYZ').

--
http://www.5valleys.com/
Paul T. (Guest)
on 2008-06-01 20:30
^^
Why are they opinionated on this? You should always be able to set a
primary key and there has never been a popular db consensus that primary
keys have to be int, bigint nor autoindex'd. Yes you can disable primary
keys and create your own unique index. It just seems silly they don't
enable you to define your own primary (on the basis of opinion
allegedly). Just because it is database agnostic doesn't mean they
disregard the relational db ways , after all 'agnostic' really isn't an
accurate word but rather a buzz word. I'm using a plugin foreign key
relationships. It is good to have that extra layer.
Matthew Rudy J. (Guest)
on 2008-06-01 21:01
Paul Thomas wrote:
> ^^
> Why are they opinionated on this? You should always be able to set a
> primary key and there has never been a popular db consensus that primary
> keys have to be int, bigint nor autoindex'd. Yes you can disable primary
> keys and create your own unique index. It just seems silly they don't
> enable you to define your own primary (on the basis of opinion
> allegedly). Just because it is database agnostic doesn't mean they
> disregard the relational db ways , after all 'agnostic' really isn't an
> accurate word but rather a buzz word. I'm using a plugin foreign key
> relationships. It is good to have that extra layer.

It's difficult to redefine the primary key type.

the sqlite adaptor makes it easier by defining a
"default_primary_key_type"
http://github.com/rails/rails/tree/master/activere...
which you could easily overwrite

but more generally, the mysql connection adaptor doesnt do this.

http://github.com/rails/rails/tree/master/activere...
>> ActiveRecord::Base.connection.native_database_types[:primary_key]
=> "int(11) DEFAULT NULL auto_increment PRIMARY KEY"

but you could overwrite this in your migration, I guess.
Frederick C. (Guest)
on 2008-06-01 21:06
(Received via mailing list)
You could just write the sql to create the table

Fred
Matthew Rudy J. (Guest)
on 2008-06-01 21:34
Frederick C. wrote:
> You could just write the sql to create the table
>
> Fred

that's too easy Froddy.
Jim B. (Guest)
on 2008-07-12 21:11
Frederick C. wrote:
> You could just write the sql to create the table
>
> Fred

I've done something similar for legacy DB that cannot follow the Rails
convention explicitly in postgreSQL in the past:-

So, in the migrations file:-

  def self.up
      # -----------------------------------------------------
      # This is example postgreSQL specific DDL, have a think
      # what is right for you
      # -----------------------------------------------------
      execute <<-EOF
        create table public.<my_pluralized_model_name> (
            mykey varchar(255) not null unique,
            firstvalue varchar(255) not null,
            secondvalue varchar(2000),
            primary key (mykey)
        );
      EOF
  end

So, substitute <my_pluralized_model_name> with your model name
"pluralized".
You should know the score on this one by now.


You also seem to need to add a line in the model file itself to allow
rails to
pick up the new string identifier "mykey" as the ID:-

So .......

class MyModelName < ActiveRecord::Base
    set_primary_key "mykey"
end

Then, if you used the standard script/generate scaffold MyModelName,
you'll have
a controller.  All methods of which will work (for you to test with),
apart from the new, which I'm trying to figure at them moment.

With a non-standard PK defined at the table level, I'm getting

"WARNING: Can't mass-assign these protected attributes: mykey"

I reckon this is because by default rails doesn't like assignment of
PKs, but instead likes to assign PK values itself using aut-incrementing
DB specific single instance mechs, which don't scale.

If there are any serious Rails dudes out there who know how I can crack
that last bit, it would save going back to the customer and persuading
them to re-engineer a legacy DB (which they won't do)

Anyways, HTH
Jim B. (Guest)
on 2008-07-12 22:23
Jim Ball wrote:
> Frederick C. wrote:
>> You could just write the sql to create the table
>>
>> Fred
>
> I've done something similar for legacy DB that cannot follow the Rails
> convention explicitly in postgreSQL in the past:-
>
> So, in the migrations file:-
>
>   def self.up
>       # -----------------------------------------------------
>       # This is example postgreSQL specific DDL, have a think
>       # what is right for you
>       # -----------------------------------------------------
>       execute <<-EOF
>         create table public.<my_pluralized_model_name> (
>             mykey varchar(255) not null unique,
>             firstvalue varchar(255) not null,
>             secondvalue varchar(2000),
>             primary key (mykey)
>         );
>       EOF
>   end
>
> So, substitute <my_pluralized_model_name> with your model name
> "pluralized".
> You should know the score on this one by now.
>
>
> You also seem to need to add a line in the model file itself to allow
> rails to
> pick up the new string identifier "mykey" as the ID:-
>
> So .......
>
> class MyModelName < ActiveRecord::Base
>     set_primary_key "mykey"
> end
>
> Then, if you used the standard script/generate scaffold MyModelName,
> you'll have
> a controller.  All methods of which will work (for you to test with),
> apart from the new, which I'm trying to figure at them moment.
>
> With a non-standard PK defined at the table level, I'm getting
>
> "WARNING: Can't mass-assign these protected attributes: mykey"
>
> I reckon this is because by default rails doesn't like assignment of
> PKs, but instead likes to assign PK values itself using aut-incrementing
> DB specific single instance mechs, which don't scale.
>
> If there are any serious Rails dudes out there who know how I can crack
> that last bit, it would save going back to the customer and persuading
> them to re-engineer a legacy DB (which they won't do)
>
> Anyways, HTH

Right figured it myself.

The default controller you get for CRUD uses mass assignment in its
create method, we know this fails, so avoid it.

Instead of the default mass assignment in the create method:-

    @mymodel = MyModel.new(params[:mymodel])

Do the "new" first, and then pick out your model attribute values from
the
params passed into the create method, and then save:-

    @mymodel = MyModel.new

    got_details = params[:mymodel]
    @my_model.mykey = gotdetails["mykey"]
    @my_model.firstvalue = gotdetails["firstvalue"]
    @my_model.secondvalue = gotdetails["secondvalue"]

    @my_model.save

Okay, that will do it.
Noah D. (Guest)
on 2010-10-19 21:51
Frederick C. wrote in post #682577:
> You could just write the sql to create the table
>
> Fred

True, but be careful if you plan on using schema.rb to deploy your
application.  schema.rb is generated by rails from the database state.
If you use a non integer primary key in your database, schema.rb will
still generate an integer column.
Marnen L. (Guest)
on 2010-10-19 22:21
Paul Thomas wrote in post #682570:
> ^^
> Why are they opinionated on this? You should always be able to set a
> primary key and there has never been a popular db consensus that primary
> keys have to be int, bigint nor autoindex'd.

Actually, there has been.  Surrogate primary keys should always be
automatically generated, and the use of a surrogate primary key (rather
than a domain key) has the advantage that you know it will never ever
change over the life of the record.

And the easiest way to autogenerate surrogate keys is to use an
incrementing integer or GUID.

> Yes you can disable primary
> keys and create your own unique index. It just seems silly they don't
> enable you to define your own primary (on the basis of opinion
> allegedly).

It's not silly.  You don't ever need to define your own primary key.

> Just because it is database agnostic doesn't mean they
> disregard the relational db ways ,

Right!  No relational DB practices are being disregarded here.

> after all 'agnostic' really isn't an
> accurate word but rather a buzz word.

Wrong.

> I'm using a plugin foreign key
> relationships. It is good to have that extra layer.

That creates foreign key constraints in the DB, right?  If so, then it's
completely irrelevant to this discussion.

(For the record, I use Foreigner for the same purpose.)

Best,
--
Marnen Laibow-Koser
http://www.marnen.org
removed_email_address@domain.invalid
Marnen L. (Guest)
on 2010-10-19 22:21
Noah D. wrote in post #955514:
> Frederick C. wrote in post #682577:
>> You could just write the sql to create the table
>>
>> Fred
>
> True, but be careful if you plan on using schema.rb to deploy your
> application.  schema.rb is generated by rails from the database state.
> If you use a non integer primary key in your database, schema.rb will
> still generate an integer column.

Interesting.  Could you get around this by setting the schema language
to SQL (not that I advocate that)?

Best,
--
Marnen Laibow-Koser
http://www.marnen.org
removed_email_address@domain.invalid
This topic is locked and can not be replied to.