non integer primary key

rubyonrails-talk
1

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

2

migo wrote:

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

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').

3

^^ 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.

4

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/activerecord/lib/active_record/connection_adapters/sqlite_adapter.rb#LC374 which you could easily overwrite

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

http://github.com/rails/rails/tree/master/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb#LC194

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.

5

You could just write the sql to create the table

Fred

6

Frederick Cheung wrote:

You could just write the sql to create the table

Fred

that's too easy Froddy.

7

Frederick Cheung 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

8

Jim Ball wrote:

Frederick Cheung 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.

9

Frederick Cheung 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.

10

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,

11

Noah D. wrote in post #955514:

Frederick Cheung 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,