No auto-increment field


#1

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?


#2

Luke I. removed_email_address@domain.invalid 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. :slight_smile:

Cheers,
Tyler


#3

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:


#4

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