No auto-increment field

I have a table that has no primary key… it is a cross-ref table with some extra data stored in it.

I’d prefer not to use a primary key because the size of this table will grow so rapidly that I’m afraid, over time, I will exceed the sequence.

When I create a new row in the table, and persist it, it works fine.
When I try to update the same row, it throws an error:

Unknown column 'id' in 'where clause': UPDATE order_details SET `order_id` = 5, `quantity` = 2, `item_id` = 1, `internal_cost_at_sale` = NULL, `customer_price_at_sale` = NULL WHERE id = NULL

Of course id is an unknown column… it’s not even supposed to be doing anything with it. Is there some way I can tell this not to do what it’s doing?

Well, you should still have a primary key, to help the database pull your
rows out quickly. I'm guessing you'd want

  PRIMARY KEY (order_id, item_id)

  but you may want (item_id, order_id) - it depends on which one you're
looking up more often. Either way, it looks like what you want is a WHERE
clause like

  WHERE (order_id, item_id) = (5, 1)

  You could probably pull this off by defining your own private "update"
method in your model... The ActiveRecord default method is:

  private
     def update
        connection.update(
          "UPDATE #{self.class.table_name} " +
          "SET #{quoted_comma_pair_list(connection,
          attributes_with_quotes(false))} " +
          "WHERE #{self.class.primary_key} = #{quote(id)}",
          "#{self.class.name} Update"
        )

        return true
      end

  ... so just copy and paste that into your model and tweak it for your
needs. :slight_smile:

  Cheers,
    Tyler

I basically knew I could do that, but sometimes you get burnt out and seeing someone else write something out is just what you need.

Thanks :slight_smile:

Luke Ivers wrote the following on 14.03.2007 17:28 :

I have a table that has no primary key... it is a cross-ref table with
some extra data stored in it.

I'd prefer not to use a primary key because the size of this table
will grow so rapidly that I'm afraid, over time, I will exceed the
sequence.

You can use a 64 bit integer type for the id column. Bigint is available
for PostgreSQL and MySQL, SQLite adapts itself to the value but you
don't want SQLite for a database which can need more than 32bit for
storing ids.
Rails by default create simple int columns (INTEGER/INT) but you can
create a hand-crafted migration that converts the 32bit column to a 64
bit one.

Lionel