SQLite migration removes 'add_index' and 'add_foreign_key' from schema.rb file

Question

I use SQLite3 and MySQL on the same Rails project but on two different computers. I noticed that the schema.rb which is generated when I run all migrations looks different for both environments. When I run the migrations in the SQLite3 environment the following statements are removed from the schema.rb file.

add_index "places", ["user_id"], :name => "places_user_id_fk"
add_foreign_key "places", "users", :name => "places_user_id_fk"

Please note, that I use the foreigner gem which extends migrations with add_foreign_key and remove_foreign_key.

Here are the migrations and model relevant to the problem:

# 20130123004056_create_places.rb
class CreatePlaces < ActiveRecord::Migration
  def change
    create_table :places do |t|
      t.string :name
      t.string :location
      t.integer :user_id
      t.timestamps
    end
  end
end

...

# 20130123234250_add_foreign_key.rb
class AddForeignKey < ActiveRecord::Migration
  def change
    add_foreign_key(:places, :users)
  end
end

...

# user.rb
class User < ActiveRecord::Base
  has_many :places
end

...

# place.rb
class Place < ActiveRecord::Base
  belongs_to :user    
end

Question: How can I define the relationship between users and places in a way both SQLite3 and MySQL can handle it?

Answer 1

The foreigner README clearly states

The following adapters are supported:

• sqlite (foreign key methods are a no-op)

So your SQLite database does not have foreign key constraints set up because foreigner doesn't support them. When db/schema.rb is generated from the SQLite database, this is why there are no foreign keys specified.

The Rails Guide on Migrations mentions foreign keys quite a bit

If you need to perform tasks specific to your database (for example create a foreign key constraint) then the execute method allows you to execute arbitrary SQL

There is even an example of how to add/remove a foreign key.

Having used foreigner when first starting out with Rails, I suggest you drop it from your Gemfile and either

1. Don't use foreign keys at all; just specify your cascades on your ActiveRecord associations
2. Use the execute migration method as described in the linked example above (and just make sure all the different RDBMS' support whatever you put into the execute methods)
3. Stop using SQLite in development if you insist on using foreign keys. You're not going to use SQLite in production anyway, and are already using better RDMS (MySQL, "better" in the sense that it has the foreign key support you are looking for) on other machines.

As you pointed out in the comments, SQLite has lacking support for adding foreign keys after the table has been created; they cannot be added through a futre migration in Rails. I personally suggest you use choice 1 or 3, as it is going to be more difficult to create a solution through execute commands in migrations that satisfies the restrictions of SQLite while having the same end result on other RDMS'.