Non Numeric IDs

So, I've seen several references to Rails' preference for numeric IDs, and a couple places suggesting that not having them will cause problems. I have legacy (and frankly prefer) random alphanum strings. Ultimately isn't the primary key just seen as a string anyway? So far I don't see the big deal. "So far." So, other than the snickers of David, what problems am I going to run into by having random string keys?

-- gw (www.railsdev.ws)

Hi there. In my experience, you'll run into a couple of issues:

1) How do you guarantee the string is unique? I'm guessing that you need to create a random string, validate that it is not already existent in the database, then recreate it until you find a unique key. This is slower than numeric auto incrementing keys

2) Storage of numeric keys is faster and smaller than alphanumerics

3) Rails is built around numeric primary keys. This might not matter in your case since you are using a legacy database, but with a new pure Rails db, its better to follow the conventions.

Chris, thanks for taking a stab, but I'm still looking for a more detailed cause->effect.

Hi there. In my experience, you'll run into a couple of issues:

1) How do you guarantee the string is unique? I'm guessing that you need to create a random string, validate that it is not already existent in the database, then recreate it until you find a unique key. This is slower than numeric auto incrementing keys

2) Storage of numeric keys is faster and smaller than alphanumerics

I've worked with alphanum keys for many years across many apps, so I've been through all the above types of debates. These are reasons why some people prefer numerics, but they're not reasons why alphanumerics won't work in Rails.

3) Rails is built around numeric primary keys. This might not matter in your case since you are using a legacy database, but with a new pure Rails db, its better to follow the conventions.

Not trying to be a dweeb, but this is the type of statement I keep seeing. There's no technical facts in there which identifies the conditions under which a non-numeric key will cause Rails to cough up a lung.

Convention is one thing, but where will Rails actually break if it is not followed?

Greg -

Chris, thanks for taking a stab, but I'm still looking for a more detailed cause->effect.

Hi there. In my experience, you'll run into a couple of issues:

1) How do you guarantee the string is unique? I'm guessing that you need to create a random string, validate that it is not already existent in the database, then recreate it until you find a unique key. This is slower than numeric auto incrementing keys

2) Storage of numeric keys is faster and smaller than alphanumerics

I've worked with alphanum keys for many years across many apps, so I've been through all the above types of debates. These are reasons why some people prefer numerics, but they're not reasons why alphanumerics won't work in Rails.

3) Rails is built around numeric primary keys. This might not matter in your case since you are using a legacy database, but with a new pure Rails db, its better to follow the conventions.

Not trying to be a dweeb, but this is the type of statement I keep seeing. There's no technical facts in there which identifies the conditions under which a non-numeric key will cause Rails to cough up a lung.

Convention is one thing, but where will Rails actually break if it is not followed?

So, I've seen several references to Rails' preference for numeric IDs, and a couple places suggesting that not having them will cause problems. I have legacy (and frankly prefer) random alphanum strings. Ultimately isn't the primary key just seen as a string anyway? So far I don't see the big deal. "So far." So, other than the snickers of David, what problems am I going to run into by having random string keys?

-- gw (www.railsdev.ws)

Try in the console

Model.find("#{my_numeric_id}") # works Model.find("#{my_numeric_id}ANY_STRING") # works, and returns the same instance as above

lesson: the finders (not sure about all), strip off non-numeric characters from the end of the ID field

which would be a really good reason why non-numeric PK's will bail.

Jodi

  1. Rails is built around numeric primary keys. This might not matter in your case since you are using a legacy database, but with a new pure Rails db, its better to follow the conventions.

Not trying to be a dweeb, but this is the type of statement I keep seeing. There’s no technical facts in there which identifies the conditions under which a non-numeric key will cause Rails to cough up a lung.

Convention is one thing, but where will Rails actually break if it is not followed?

I believe that’s just it - no effort has been spent testing Rails w/ non-integer primary keys since Rails doesn’t use them (opinionated software, and all that), so nobody will be able to give you a definitive answer as to where it will break. You want random alphanumerics? Go for it. It breaks and you get to keep both pieces =) (seriously, though, if it breaks, I’d submit patches – unless it’s a big departure, I’d expect them to get rolled in).

-b

I'm not getting same results as you. Seems that no matter how I slice up the ID part, I get the record I expect.

I created records 99, 99abc, 99xyz, 99_abc.

Using these tests:

modelID = "99"; PrivilegedUser.find(modelID).userFirstName modelID = "99abc"; PrivilegedUser.find(modelID).userFirstName modelID = "99xyz"; PrivilegedUser.find(modelID).userFirstName modelID = "99_abc"; PrivilegedUser.find(modelID).userFirstName

modelID = "99"; PrivilegedUser.find("#{modelID}").userFirstName modelID = "99abc"; PrivilegedUser.find("#{modelID}").userFirstName modelID = "99xyz"; PrivilegedUser.find("#{modelID}").userFirstName modelID = "99_abc"; PrivilegedUser.find("#{modelID}").userFirstName

modelID = "99"; PrivilegedUser.find("#{modelID}abc").userFirstName modelID = "99"; PrivilegedUser.find("#{modelID}xyz").userFirstName modelID = "99"; PrivilegedUser.find("#{modelID}_abc").userFirstName

It all works fine for me.

-- gw (www.railsdev.ws)