Urgent: Primary key issue

Rails
1

It wasn’t even 4-5 hours after I could resolve a problem regarding
error_messages_for, I ran into a new issue.

I have a table whose primary key is set to email address and there is
not need for an auto incremented number in the table. I’ve updated the
model, with the info, as follows:

  set_primary_key 'email_address'

Now, I have a registration form, which will post all the needed info
to the controller, along with the email address.

In the controller, I’m creating a new record:

@user = User.create ( :email_address =>
params[:email_address], :name => params[:name])
@user.password = ‘xyz’
if !@user.save
puts “An error occured, while trying to create an account for
#{params[:email_address]}”
end

The create method call never succeeds, as it is passing nil to email,
even if params[:email_address] contains the actual value (I’ve
verified this by the printing it!) passed.

If I remove the set_primary_key in the model definition and try the
action again, a record gets created with email and name properly set,
however, after setting password (which also succeeds) and then trying
to save I get an error.

The log shows something like:
There is no field id in the table: UPDATE users SET email_address =
‘[email protected]’, name = ‘ABCD’, password = ‘xyz’ where id = 0

I wonder where this id = 0 is coming from? None of the two methods
work for me. Am I missing anything here? Your help would be very
invaluable. Thank you a ton in advance.

2

askme wrote:

to the controller, along with the email address.

There is no field id in the table: UPDATE users SET email_address =
‘[email protected]’, name = ‘ABCD’, password = ‘xyz’ where id = 0

I wonder where this id = 0 is coming from? None of the two methods
work for me. Am I missing anything here? Your help would be very
invaluable. Thank you a ton in advance.

What does your migration for that model look like? You probably have an
id column in your database table if you didn’t specifically exclude it
in your migration definition as in:

create_table :users, :id => false do |t|
t.column :email_address, :string, :limit => 120, :null => false
…
end

In other words, in a migration Rails will always create an id column
even if you don’t specify one unless you include :id => false.

–
Michael W.

3

I wonder where this id = 0 is coming from? None of the two methods
work for me. Am I missing anything here? Your help would be very
invaluable. Thank you a ton in advance.

I don’t have the answer you’re looking for, though I’m sure someone
will be along soon with some good tips.

But I would question why you are doing it this way. My experiences
with Rails have been that “if i follow the rules it will be easy”.

Active Record is built around the idea that each table is an auto
increment primary key column called ‘id’. And if I follow that
convention I can use these powerful associations, etc, with little
hassle.

Unless you’re dealing with a legacy database ~ why not use use the id
column and specify validates_uniqueness of :email_address. That’ll
give you unique email addresses and you won’t have to customise each
association you want to create down the line.

my 2p

4

michael,

yes, i’ve specified :id => false in the migration and also, added an
index over email_address which becomes the primary key by default (if
using mysql. don’t about the other databases, though).

tobb,
thanks for the good suggestion, but i’m afraid that doesn’t work for
me in the near future. i understand going rails way is easy but the
tables i’m going to work with definitely requires a non-integer
primary key. an integer key doesn’t make any sense on them at all.
Here’s one such scenario (though a bad example).

I have the following tables:

  1. Company ( primary key - id, name)
  2. Products ( primary key - product_id, name)
  3. Sales ( primary key - id, product_id, number_of_items_sold)

I’ve a requirement as follows:

Company’s id is an auto_incremented integer like 1, 2 …
Product id is a two tuple integer like 1.1, 1.2 where the first
number indicates the company’s id and the second number indicates the
'n’th product produced by that company.

e.g. Company : 1 | Gillete
Products: 1.1| Shaving gel
1.2| Mach III

    .. and so on ..

For the products table the id can’t be an integer at all, and also
the users of this application are very very used to refer products by
such ids.

thank you both for looking into my problem. any suggestions are
welcome.

5

Why is this marked Urgent?

6

Hi dysinger, I marked it urgent, as i’m struck and can’t move on
without having this resolved.

7

askme wrote the following on 10.03.2007 13:05 :

primary key.
You are confusing primary keys and data constraints.

an integer key doesn’t make any sense on them at all.

For your data no, for the layer you build on top of it to access and
manipulate it, it does.

Company’s id is an auto_incremented integer like 1, 2 …
Product id is a two tuple integer like 1.1, 1.2 where the first
number indicates the company’s id and the second number indicates the
'n’th product produced by that company.

e.g. Company : 1 | Gillete
Products: 1.1| Shaving gel
1.2| Mach III

    .. and so on ..

Wow… I don’t know where to begin. This way of designing your database
schema is so bizarre that I don’t know where it can come from?!

Your products should reference your company, so it should have a
company_id where you’ll store the first part of your ‘tuple’. Then it
will have it’s own unique id. Don’t tell me you plan of listing a
company’s product by doing hand-crafted SQL selects … WHERE product_id
LIKE ‘.%’
That’s just plain wrong on so many accounts!

It seems to me that you’re not yet familiar with database design theory.
If I’m right, you definitely should take some time studying it before
continuing the development. Given the path you take, all Rails benefits
(at least the ones from the database access layer) go away because they
are built on top of database design practices you aren’t following.

Lionel.

8

On Sat, 2007-03-10 at 04:05 -0800, askme wrote:

primary key. an integer key doesn’t make any sense on them at all.
Company’s id is an auto_incremented integer like 1, 2 …
For the products table the id can’t be an integer at all, and also
the users of this application are very very used to refer products by
such ids.

thank you both for looking into my problem. any suggestions are
welcome.

I guess that I simply succumbed to the simplicity of accepting an
integer based ‘id’ as the primary key for all of my tables and thus have
to wonder why you think it is so important to fight it. There’s no
reason why for example, your Products couldn’t have both an ‘id’ and
‘product_id’ column making the ‘id’ column the primary key and leaving
your ‘product_id’ column to be anything you define it to be including a
unique validation.

Craig

9

On Sun, 2007-03-11 at 02:11 +0000, askme wrote:

Lionel/ Craig,

I understand that. I also agree having an ID doesn’t harm my DB
design except for space. Forget about the ‘bad’ db design, I was only
wondering to understand what advantage I’d get using an integer for
ID when I already have another unique column. In my Database Design
course at univ, I was never thought to use an AUTO_INC INT only as
PRIMARY KEY, when there is another field, which is unique and the
records are picked using them only.

it’s your system and you can do as you please.

with rails and a primary key called ‘id’ that is integer with an
attached sequence to increment its value makes everything easy - i.e.,
there is no coding necessary.

Once you deviate, you become responsible for all of the coding necessary
to create/edit and maintain the primary key values which is much more
difficult for someone new to rails.

As for database design, I’ll let Lionel’s comments stand on their own
but database design must take into account not only the SQL backend but
also the api of the middleware.

Craig

10

As for database design, I’ll let Lionel’s comments stand on their own
but database design must take into account not only the SQL backend but
also the api of the middleware.

Thanks for this, I’ll use a dummy integer field as an ID just to take
the advantage of rails.

11

Don’t do it just for rails. Identity field is a well-established pattern
and generally considered a “best practice”, regardless of what stodgy
old database profs (who likely never wrote a line of code) have to say.
:slight_smile: I used it on every Java app I wrote.

http://www.martinfowler.com/eaaCatalog/identityField.html

b

12

Lionel/ Craig,

I understand that. I also agree having an ID doesn’t harm my DB
design except for space. Forget about the ‘bad’ db design, I was only
wondering to understand what advantage I’d get using an integer for
ID when I already have another unique column. In my Database Design
course at univ, I was never thought to use an AUTO_INC INT only as
PRIMARY KEY, when there is another field, which is unique and the
records are picked using them only.

  • Chaitanya

On Mar 10, 9:58 pm, Lionel B. [email protected]

13

I recently had to work with a legacy database table that used a
non-integer primary key. To deal with the problem, I ended up writing
some custom class methods that build raw SQL queries. If you’re
interested, I posted my custom methods on my blog, and you can check
them out here:

http://joeyschoblaska.com/posts/ruby-on-rails-working-with-a-legacy-database-with-a-non-integer-primary-key-20/

14

There are other advantages of simple incrementing primary keys that
have not yet been mentioned. I have run into this countless time with
legacy databases that someone else designed. It will inevitably happen
that a customer, due to changes in their business rules, will decide
that they absolutely must change their product item numbers for some
very important reason. Now if you have assumed that fields like a
product item number are natural primary keys, you have a huge problem
since you would have all these associations with other tables that
must be modified in order to satisfy the requirement of the “real
world” business rule change.

This becomes a very minor issue when a separate identity column is
used for associations between tables. Simply replace the product item
numbers with new numbers ensuring that each are unique and that is
all. You’re associations don’t have to be touched at all.

Secondly, having simple integer primary keys is great for object
relational mapping. Each object has a simple id that has a one-to-one
correspondence with the table row representing the persistent state of
object instance. This is why it makes so much sense to have these
“identity” columns for each database table. Of course, there is one
exception in the case of join tables for many-to-many relations where
a compound primary key (made up of two simple integer keys) are often
preferred. The purpose here is to ensure uniqueness of each row used
to join to the two master tables. This is accomplished using a unique
index across the two columns of the compound key. But, that’s really
just more database 101.

On Sep 4, 9:19 pm, Joey S. [email protected]