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?
Luke I. [email protected] wrote:
I have a table that has no primary key… it is a cross-ref table with some
extra data stored in it.
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
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 I. 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