Has_and_belongs_to_many Sqlite3 headache: "SQL logic error or missing database"

What?

How to get many to many relations working quickly with Rails 2.0.2 and sqlite3 3.4.0.

Why?

Many to Many database relations between two models (tables) requires a middle table which records the relation. This can be difficult to get right, but Rails has some awesome helper functions to get you up and running quickly.

... when it works.

When it doesn't, you're likely to see the following:

"SQL logic error or missing database"
Which is about as descriptive as "it's not working." The kink is that Active Record (rails' database interface) will try to set the id field of the relational table, which is reserved by default as the primary key and therefore cannot be set to the same value twice (which rails will do).

How?

First create the models

./script/generate scaffold Actor first_name:string last_name:string
./script/generate scaffold Movie name:string

Then create a migration to set up the relational table:

./script/generate migration AddActorsMoviesRelation

Edit ./db/migration/xyz_AddActorsMoviesRelation.rb:
(Thanks EyeDeal for the :id => false tip!)

class AddActorsMoviesRelation < ActiveRecord::Migration
  def self.up 
    create_table :actors_movies, :id => false do |t|
      t.integer :actor_id, :foreign_key => true
      t.integer :movie_id, :foreign_key => true
    end
  end

  def self.down
    drop_table :actors_movies
  end
end

Then build the database

rake db:migrate

And finally edit ./app/models/actor.rb and ./app/models/movie.rb:

class Actor < ActiveRecord::Base
  has_and_belongs_to_many :movies
end

class Movie < ActiveRecord::Base
  has_and_belongs_to_many :movies
end

You should now be able to add movies to actors and vice versa with a simple

crime_fiction = Movie.create :name => "Crime Fiction"
san_fransisco = Movie.create :name => "The Streets of San Francisco"
jon = Actor.create :first_name => 'Jonathan', :last_name => 'Elliot'
jesse = Actor.create :first_name => 'Jesse', :last_name => 'Friedman'
dan = Actor.create :first_name => 'Dan', :last_name => 'Bakkedahl'

crime_fiction.actors << jon
jesse.movies << crime_fiction
dan.movies << crime_fiction

crime_fiction.actors.each {|actor| puts "#{actor.last_name}, #{actor.first_name}" }
# => Elliot, Jonathan
# => Friedman, Jesse
# => Bakkedahl, Dan

san_fransisco.actors << jesse
jesse.movies.each {|movie| puts movie.name }
# => Crime Fiction
# => The Streets of San Francisco