SQLite3/AR failing to increment PK on habtm table.

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):   > .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

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.

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)

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.