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. 
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
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