0

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?

JJD
  • 50,076
  • 60
  • 203
  • 339

1 Answers1

0

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'.

Community
  • 1
  • 1
deefour
  • 34,974
  • 7
  • 97
  • 90
  • As [posted here](http://www.ruby-forum.com/topic/3875433) I run into the problem that the migration containing manual SQL code does throw an exception. – JJD Jan 25 '13 at 15:51
  • You should post issues with that as another question; it's a bit outside the scope of this. – deefour Jan 25 '13 at 19:02
  • I have to revoke the "correct answer" flag from your post. SQLite does not support altering the table after it has been created. From the [SQLite documentation](http://www.sqlite.org/omitted.html): "[...] Other kinds of ALTER TABLE operations such as [...] ADD CONSTRAINT, and so forth are omitted." The same issue has been discussed [here](http://stackoverflow.com/a/1884841/356895). – JJD Jan 26 '13 at 16:28
  • I have modified my answer to provide better guidance with choice 2 (what you seem to have stuck with) and offered a 3rd choice. My diagnoses of the **cause** of your problem was correct (you using foreigner). I fixed the wording of choice 2; it was not generic enough. **Whatever** goes in the `execute` (as you found, adding foreign keys this way won't work with SQLite) must be supported by all RDBMS. I cannot provide a solution for what you should put in the `execute` lines because I personally thing it's the wrong thing to do. If that means my answer isn't correct enough for you, that's cool. – deefour Jan 26 '13 at 20:34
  • I agree. I ended up removing the foreign keys. I my project I use [inherited_resources](https://github.com/josevalim/inherited_resources) which takes care of the referential integrity I was looking for. Thank you. – JJD Jan 26 '13 at 21:56