Sqlite Foreigner

Sqlite Foreigner is a Rails foreign key migration helper which supports adding AND enforcing foreign key constraints on Sqlite3 databases.

The Story

With a lack of support for easily adding foreign key constraints to sqlite databases I decided to create my own based on Matt Higgins Foreigner

Some Examples

Sqlite Foreigner allows you to do the following in your migration files


  create_table :comments do |t|
    t.references :posts, :foreign_key => true, :null => false
  end

Which will generate the following SQL:


  <span class="caps">CREATE</span> <span class="caps">TABLE</span> “comments” (“id” <span class="caps">INTEGER</span> <span class="caps">PRIMARY</span> <span class="caps">KEY</span> <span class="caps">AUTOINCREMENT</span> <span class="caps">NOT</span> <span class="caps">NULL</span>,
  “post_id” integer <span class="caps">NOT</span> <span class="caps">NULL</span>,
  <span class="caps">FOREIGN</span> <span class="caps">KEY</span> (“post_id”) <span class="caps">REFERENCES</span> “posts”(id));

Go a different column name?


  create_table :comments do |t|
    t.references :article, :null => false
    t.foreign_key :posts, :column => :article_id
  end

Which generates:


  <span class="caps">CREATE</span> <span class="caps">TABLE</span> “comments” (“id” <span class="caps">INTEGER</span> <span class="caps">PRIMARY</span> <span class="caps">KEY</span> <span class="caps">AUTOINCREMENT</span> <span class="caps">NOT</span> <span class="caps">NULL</span>,
  “article_id” integer <span class="caps">NOT</span> <span class="caps">NULL</span>,
  <span class="caps">FOREIGN</span> <span class="caps">KEY</span> (“article_id”) <span class="caps">REFERENCES</span> “posts”(id));

Want to specify a dependency (nullify or delete)?


  create_table :comments do |t|
    t.references :posts, :foreign_key => {:dependent => :delete}, :null => false
  end

Generates:


  <span class="caps">CREATE</span> <span class="caps">TABLE</span> “comments” (“id” <span class="caps">INTEGER</span> <span class="caps">PRIMARY</span> <span class="caps">KEY</span> <span class="caps">AUTOINCREMENT</span> <span class="caps">NOT</span> <span class="caps">NULL</span>,
  “post_id” integer <span class="caps">NOT</span> <span class="caps">NULL</span>,
  <span class="caps">FOREIGN</span> <span class="caps">KEY</span> (“post_id”) <span class="caps">REFERENCES</span> “posts”(id) ON <span class="caps">DELETE</span> <span class="caps">CASCADE</span>);

Or:


  create_table :comments do |t|
    t.references :article, :null => false
    t.foreign_key :posts, :column => :article_id, :dependent => :nullify
  end

Which generates:


  <span class="caps">CREATE</span> <span class="caps">TABLE</span> “comments” (“id” <span class="caps">INTEGER</span> <span class="caps">PRIMARY</span> <span class="caps">KEY</span> <span class="caps">AUTOINCREMENT</span> <span class="caps">NOT</span> <span class="caps">NULL</span>,
  “article_id” integer <span class="caps">NOT</span> <span class="caps">NULL</span>,
  <span class="caps">FOREIGN</span> <span class="caps">KEY</span> (“article_id”) <span class="caps">REFERENCES</span> “posts”(id) ON <span class="caps">DELETE</span> <span class="caps">SET</span> <span class="caps">NULL</span>);

Enforcing constraints

SQLite 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:


  script/dbconsole
  .genfkey —exec

While your in the console run:


  .schema

to see your constraints implemented as triggers

schema.rb

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


  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 SQLite does not have complete ALTER TABLE support you cannot use the following syntax:


  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

Add the following to environment.rb:


  config.gem “sqlite-foreigner”, :lib => “foreigner”, :source => “http://gemcutter.org”

Then run:


  sudo rake gems:install