Urgent: Primary key issue

rubyonrails-talk
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 = 'a@b.com', 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:

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 = 'a@b.com', 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.

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

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.

8

askme wrote the following on 10.03.2007 13:05 :

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.

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.

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 ..    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 '<id>.%' 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.

9

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

10

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.

11

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.

12

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

askme wrote:

13

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.