Non integer primary key

Rails
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:

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/

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 C. wrote:

You could just write the sql to create the table

Fred

that’s too easy Froddy.

7

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.

8

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

9

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.

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,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

11

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
[email protected]