# No auto-increment field

**URL:** <https://www.ruby-forum.com/t/no-auto-increment-field/88813>\
**Category:** Rails\
**Created:** [March 14, 2007, 5:33pm UTC](https://www.ruby-forum.com/t/no-auto-increment-field/88813 "2007-03-14T17:33:27Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![technodolt](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/e99b99/32.png) [@technodolt](https://www.ruby-forum.com/u/technodolt)\
**Post date:** [March 14, 2007, 5:33pm UTC](https://www.ruby-forum.com/t/no-auto-increment-field/88813/1 "2007-03-14T17:33:27Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![technodolt](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/e99b99/32.png) [@technodolt](https://www.ruby-forum.com/u/technodolt)\
**Post date:** [March 14, 2007, 5:49pm UTC](https://www.ruby-forum.com/t/no-auto-increment-field/88813/2 "2007-03-14T17:49:32Z")

</div>

Luke I. [removed\_email\_address@domain.invalid](mailto: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. 🙂

Cheers,  
Tyler

---

<div class="post-metadata">

**Author:** ![technodolt](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/e99b99/32.png) [@technodolt](https://www.ruby-forum.com/u/technodolt)\
**Post date:** [March 14, 2007, 6:01pm UTC](https://www.ruby-forum.com/t/no-auto-increment-field/88813/3 "2007-03-14T18:01:33Z")

</div>

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 🙂

---

<div class="post-metadata">

**Author:** ![technodolt](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/e99b99/32.png) [@technodolt](https://www.ruby-forum.com/u/technodolt)\
**Post date:** [March 14, 2007, 8:52pm UTC](https://www.ruby-forum.com/t/no-auto-increment-field/88813/4 "2007-03-14T20:52:07Z")

</div>

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