Question about Primary Key

rubyonrails-talk
1

Hi!

I’ve a couple of questions about the primary key on RoR:

  1. Is there a way to use non-numeric autoincrement primary key?
  2. Is there a way to use compound primary key? If not possible, we want to work on a ruby gem to read a database and made the model from it. So, there are recommendations regard the two things above?
2

Hi!

I've a couple of questions about the primary key on RoR:

Is there a way to use non-numeric autoincrement primary key? Is there a way to use compound primary key?

Yes, but really don't do it unless you are working with a legacy db that you cannot change (absolutely definitely impossible to change). Otherwise use the default id for the primary key and add compound indexes for the compounded fields if necessary. That will be /much/ less effort.

Colin

3

Colin Law wrote in post #1091609:

Yes, but really don't do it unless you are working with a legacy db that you cannot change (absolutely definitely impossible to change). Otherwise use the default id for the primary key and add compound indexes for the compounded fields if necessary. That will be /much/ less effort.

First, I agree with this completely. I just wanted to add a bit of clarification as to why I agree with this.

An important aspect of working with Rails (or any modern Object Relational Mapping framework) is mapping an object's identity with a database table row. Rails by default uses a simple integer value, which it manages itself, to ensure that a model object always maps to a single database table row in the database. This simple integer value is then used as the primary key for the table.

As Colin says, it really is best to just let Rails have its identity column. There is no reason you cannot have your identifying column(s) on that table as well, generated by whatever means you wish. Just add a unique index to your column(s) and treat that as your key, but don't make that the table's primary key. Let Rails manage that using its own built-in identity mapping.

This technique also makes your database design less fragile. I've run into many, many cases where some business rule changes forcing all identifying columns to have to change for one reason or another. If that column(s) is used for relational mapping then multiple tables have to be updated in order to facilitate the change. Yet when surrogate primary keys are used for mapping relationships (as well as the ORM identities) this sort of change is isolated to the single table where the natural keys exist.