dwilkie Foreigner

dwilkie-foreigner is a fork of Matt Higgins Foreigner
which supports adding AND enforcing foreign key constraints on SQLite3 databases.

It also allows you to do the following in your migration files (not supported by matt higgins foreigner)

  create_table :comments do |t|

    t.references :post, :foreign_key => true, :null => false

  end

Enforcing constraints in SQLite3

SQLite3 does not enforce database constraints out of the box
This provides you with the flexibility in choosing whether or not to enforce
constraints at the DB level or not.

In order to enforce your constraints:
Rails 3

  rails dbconsole

  .genfkey —exec

Rails 2.3.x

  script/dbconsole

  .genfkey —exec

While your in the db console run:

.schema

to see your constraints implemented as triggers

Now when you try and remove a model with an association in Rails with sqlite3 you will get a database exception!

Some More Examples

To specify a different column name you can do the following:

  create_table :comments do |t|

    t.integer :article_id, :null => false

    t.foreign_key :post, :column => :article_id

  end

To specify dependencies (nullify or delete) you can do the following:

  create_table :comments do |t|

    t.references :post, :foreign_key => {:dependent => :delete}, :null => false

  end

Here’s another example using a different column name and the dependent option

  create_table :comments do |t|

    t.integer :article_id, :null => false

    t.foreign_key :post, :column => :article_id, :dependent => :nullify

  end

schema.rb

All of the constrants are updated in schema.rb when you either:

  rake db:migrate

  rake db:schema:dump

This allows you to see the state of your migratons and
take advantage of using

rake db:schema:load

Limitations

Since SQLite3 does not have complete ALTER TABLE support you cannot use the following syntax when using an SQLite3 database:

  add_foreign_key

  remove_foreign_key

Therefore you must add your foreign keys when you define your table,
which may involve editing existing migration files instead of generating new ones

Installation

Rails 3

Add the following to your Gemfile:

gem “dwilkie-foreigner”, :require => “foreigner”

Then run

bundle install

Rails 2.3.x

Add the following to environment.rb:

config.gem “dwilkie-foreigner”, :lib => “foreigner”

Then run:

sudo rake gems:install

Post Install

To enable foreign keys you also need to add a file which
includes the line

Foreigner.enable

in your config/initializers directory
You can call this file whatever you want e.g. foreigner.rb

Testing

There are built in tests for Mysql and SQLite3
In order to run them you need to install Plugin Test Helper

Copyright © 2010 David Wilkie, released under the MIT license