How to make Ruby on Rails create SQLite database with foreign keys enabled?

Question

I am newbie to Ruby on Rails and I am using SQLite3 as database for my sample project. I am trying to create ordinary one to many relationship between two models (eg. each Product has one Owner, and each Owner can have many Products). This works fine and database schema is created properly. However, when I open development.sqlite3 in database management tool (I use free SQLite Express Personal http://www.sqliteexpert.com/download.html) I don't see that database has referential integrity. There are no foreign keys listed for Product table even though it does contain owner_id column.

I tried changing database.yml by adding options key:

default: &default
adapter: sqlite3
pool: 5
timeout: 5000
options: "PRAGMA foreign_keys=ON"

And then recreate database with:

rake db:drop db:create db:migrate

This recreates database but again there is no foreign keys listed.

Am I doing something wrong? Is there solution for this at all. (PS. I am running all this on Windows 8.1 if that matters)

Cabot

Answer 1

By default, Rails doesn't create foreign key references. Rails manages foreign key values itself, using information it derives from associations in the models.

Rails 4.2 supports add_foreign_key in migrations. But you can't use that with SQLite, because SQLite doesn't support the specific ALTER TABLE statements that Rails uses to create foreign keys. SQLite has very limited support for ALTER TABLE.

Other kinds of ALTER TABLE operations such as DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT, and so forth are omitted.

Also, add_foreign_key supports only foreign key references to a single column.

If you want to learn Rails with referential integrity enforced by the dbms, you'll need to either a) switch to something like PostgreSQL or MySQL, or b) execute raw SQL in migrations. Even this will be easier with something like PostgreSQL, mainly because of its full support for standard ALTER TABLE statements.

If I were you, I wouldn't worry about foreign keys being enforced by the dbms yet. Wait until you're comfortable with the Rails environment and Rails conventions first.

---

The release notes for Rails 4.2 also say, more or less, that add_foreign_key doesn't work with SQLite yet.

The migration DSL now supports adding and removing foreign keys. They are dumped to schema.rb as well. At this time, only the mysql, mysql2 and postgresql adapters support foreign keys.

Kath

Answer 2

Here is my attempt to clear this in Rails 5.x documentation:

https://github.com/rails/rails/pull/33563

At this time, only the mysql, mysql2 and postgresql adapters support foreign keys. Implementation for sqlite3 is partial, keys are created for new tables but not for existing tables via ALTER TABLE statement.

Disaccharide