sqlite-foreigner

h1. Sqlite Foreigner

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

h2. 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":http://github.com/matthuhiggins/foreigner/

h2. 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:

  CREATE TABLE "comments" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "post_id" integer NOT NULL,
  FOREIGN KEY ("post_id") REFERENCES "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:

  CREATE TABLE "comments" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "article_id" integer NOT NULL,
  FOREIGN KEY ("article_id") REFERENCES "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:

  CREATE TABLE "comments" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "post_id" integer NOT NULL,
  FOREIGN KEY ("post_id") REFERENCES "posts"(id) ON DELETE CASCADE);

Or:

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

Which generates:

  CREATE TABLE "comments" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "article_id" integer NOT NULL,
  FOREIGN KEY ("article_id") REFERENCES "posts"(id) ON DELETE SET NULL);

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

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

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

h2. Installation

Add the following to environment.rb:

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

Then run:

  sudo rake gems:install

h2. See also

Need support for other databases? Check out "dwilkie-foreigner":http://github.com/dwilkie/foreigner/tree/master

Copyright (c) 2009 David Wilkie, released under the MIT license