dwilkie Foreigner
dwilkie-foreigner is a fork of Matt Higgins Foreigner which supports adding AND enforcing foreign key constraints on Sqlite3 databases.
Some Examples
dwilkie-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 “dwilkie-foreigner”, :lib => “foreigner”, :source => “http://gemcutter.org”
Then run
sudo rake gems:install
See also
Don’t need support for other databases? Check out sqlite-foreigner
Copyright © 2009 David Wilkie, released under the MIT license