Forum: Ruby on Rails No auto-increment field

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
2017657725dd1bce83dc8a1e2e991d04?d=identicon&s=25 Luke Ivers (Guest)
on 2007-03-14 17:33
(Received via mailing list)
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?
52cb4115a870ff1942e7e1ae96f4306f?d=identicon&s=25 Tyler MacDonald (Guest)
on 2007-03-14 17:49
(Received via mailing list)
Luke Ivers <technodolt@gmail.com> 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
2017657725dd1bce83dc8a1e2e991d04?d=identicon&s=25 Luke Ivers (Guest)
on 2007-03-14 18:01
(Received via mailing list)
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 :)
Bef7ff8a0537495a1876ffebdc9f8e51?d=identicon&s=25 Lionel Bouton (Guest)
on 2007-03-14 20:52
(Received via mailing list)
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
This topic is locked and can not be replied to.