# SQLite3/AR failing to increment PK on habtm table

**URL:** <https://www.ruby-forum.com/t/sqlite3-ar-failing-to-increment-pk-on-habtm-table/105001>\
**Category:** Rails\
**Created:** [May 24, 2007, 7:49pm UTC](https://www.ruby-forum.com/t/sqlite3-ar-failing-to-increment-pk-on-habtm-table/105001 "2007-05-24T19:49:50Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![phrogz](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/2acd7d/32.png) [@phrogz](https://www.ruby-forum.com/u/phrogz)\
**Post date:** [May 24, 2007, 7:49pm UTC](https://www.ruby-forum.com/t/sqlite3-ar-failing-to-increment-pk-on-habtm-table/105001/1 "2007-05-24T19:49:50Z")

</div>

SUMMARY  
Rails is generating SQL for a habtm insert that isn’t properly  
incrementing the primary key on my habtm table.

DETAILS  
I have a Story model like this:  
class Story \< ActiveRecord::Base  
has\_and\_belongs\_to\_many :products  
end  
with a habtm table created named “products\_stories”. Here’s the schema  
reported for that table by the db (sqlite3):  
sqlite\> .schema products\_stories  
CREATE TABLE products\_stories (“id” INTEGER PRIMARY KEY NOT NULL,  
“product\_id” integer NOT NULL, “story\_id” integer NOT NULL);

In one of my controllers I create and update lots of stories at once.  
The code looks like this:

SloppyCSV.parse( params[‘csv’], :headers=\>true ){ |story\_row|  
# …create a story instance here and populate it

```
unless story.save
  errors << story.errors
end

story.product_ids = story_row['product_ids'] &&

```

story\_row[‘product\_ids’].split(’|’).map{ |s| s.to\_i }  
story.save  
}

The first story it hits that has a product id properly inserts a row  
in the habtm join table:  
INSERT INTO products\_stories (“product\_id”, “story\_id”, “id”) VALUES  
(1, 10, 1)

However, when it hits the next story, however, it tries to insert a  
row with a conflicting id in the table:  
INSERT INTO products\_stories (“product\_id”, “story\_id”, “id”) VALUES  
(1, 11, 1)

Why is rails/sqlite3 not properly incrementing the id?

ENVIRONMENT  
This is on Mac OS X 10.4.9 on an Intel-based MacBook Pro, with a self-  
compiled install of Ruby 1.8.6, self-compiled version of SQLite3, and  
gem installs of rails and sqlite3-ruby.

Slim2:/ phrogz$ uname -a  
Darwin Slim2.local 8.9.1 Darwin Kernel Version 8.9.1: Thu Feb 22  
20:55:00 PST 2007; root:xnu-792.18.15~1/RELEASE\_I386 i386 i386

Slim2:/ phrogz$ which ruby  
/usr/local/bin/ruby

Slim2:/ phrogz$ ruby -v  
ruby 1.8.6 (2007-03-13 patchlevel 0) [i686-darwin8.9.1]

Slim2:/ phrogz$ rails -v  
Rails 1.2.3

Slim2:/ phrogz$ sqlite3 -version  
3.3.17

---

<div class="post-metadata">

**Author:** ![phrogz](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/2acd7d/32.png) [@phrogz](https://www.ruby-forum.com/u/phrogz)\
**Post date:** [May 24, 2007, 9:38pm UTC](https://www.ruby-forum.com/t/sqlite3-ar-failing-to-increment-pk-on-habtm-table/105001/2 "2007-05-24T21:38:14Z")

</div>

On May 24, 11:48 am, Phrogz [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> SUMMARY  
> Rails is generating SQL for a habtm insert that isn’t properly  
> incrementing the primary key on my habtm table.

Forgot to mention - also have installed SWIG before building the  
sqlite3-ruby gem. This problem also rears its head on my Windows box,  
so it’s not that funky SWIG thing.

---

<div class="post-metadata">

**Author:** ![phrogz](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/2acd7d/32.png) [@phrogz](https://www.ruby-forum.com/u/phrogz)\
**Post date:** [May 25, 2007, 12:05am UTC](https://www.ruby-forum.com/t/sqlite3-ar-failing-to-increment-pk-on-habtm-table/105001/3 "2007-05-25T00:05:31Z")

</div>

On May 24, 11:48 am, Phrogz [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> SUMMARY  
> Rails is generating SQL for a habtm insert that isn’t properly  
> incrementing the primary key on my habtm table.

Here’s a simpler example than the original post:

Slim2:~/Desktop/StoryWeave phrogz$ script/console  
Loading development environment.

> > s1,s2,\*\_ = Story.find(:all); nil  
> > =\> nil  
> > s1.product\_ids,s2.product\_ids = [],[]  
> > =\> [[], []]  
> > Story.connection.execute( ‘select \* from products\_stories’ )  
> > =\> []  
> > s1.product\_ids = [1]  
> > =\> [1]  
> > Story.connection.execute( ‘select \* from products\_stories’ )  
> > =\> [{0=\>“1”, 1=\>“1”, “product\_id”=\>“1”, 2=\>“1”, “story\_id”=\>“1”,  
> > “id”=\>“1”}]  
> > s2.product\_ids = [1]  
> > ActiveRecord::StatementInvalid: SQLite3::SQLException: SQL logic error  
> > or missing database: INSERT INTO products\_stories (“product\_id”,  
> > “story\_id”, “id”) VALUES (1, 2, 1)

---

<div class="post-metadata">

**Author:** ![phrogz](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/2acd7d/32.png) [@phrogz](https://www.ruby-forum.com/u/phrogz)\
**Post date:** [May 25, 2007, 7:30pm UTC](https://www.ruby-forum.com/t/sqlite3-ar-failing-to-increment-pk-on-habtm-table/105001/4 "2007-05-25T19:30:24Z")

</div>

On May 24, 4:04 pm, Phrogz [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> =\> nil
> 
> ActiveRecord::StatementInvalid: SQLite3::SQLException: SQL logic error  
> or missing database: INSERT INTO products\_stories (“product\_id”,  
> “story\_id”, “id”) VALUES (1, 2, 1)

Sorry for the bump, but…can anyone shed any light on this? I’m about  
to have to rewrite everything not to use habtm because I can’t get the  
join table to insert properly.

---

<div class="post-metadata">

**Author:** ![phrogz](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/2acd7d/32.png) [@phrogz](https://www.ruby-forum.com/u/phrogz)\
**Post date:** [May 25, 2007, 8:23pm UTC](https://www.ruby-forum.com/t/sqlite3-ar-failing-to-increment-pk-on-habtm-table/105001/5 "2007-05-25T20:23:30Z")

</div>

On May 25, 11:29 am, Phrogz [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> On May 24, 4:04 pm, Phrogz [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:
> 
> > On May 24, 11:48 am, Phrogz [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:
> 
> > > SUMMARY  
> > > Rails is generating SQL for a habtm insert that isn’t properly  
> > > incrementing the primary key on my habtm table.  
> > > Sorry for the bump, but…can anyone shed any light on this? I’m about  
> > > to have to rewrite everything not to use habtm because I can’t get the  
> > > join table to insert properly.

My apologies for the noise. The problem is simply that I should not  
have an ID column in the habtm tables involved. Removing this column  
fixed it all.