RoR: database level referential integrity

Question

I have this model:

class Book < ApplicationRecord
  has_many :pages, dependent: :destroy
end

And this one:

class Page < ApplicationRecord
  belongs_to :book
end

The migration for the Book is:

class CreateBooks < ActiveRecord::Migration[5.0]
  def change
    create_table :books do |t|
    end
  end
end

And the migration for Page is:

class CreatePages < ActiveRecord::Migration[5.0]
  def change
    create_table :pages do |t|
      t.references :book, index: true, null: false
    end
  end
  add_foreign_key :pages, :books, on_delete: :cascade
end

Additionally I got some seeds:

Book.create!(
  pages: [
    Page.new,
    Page.new,
    Page.new
  ]
)

rake db:migrate, rake db:seed and all that jazz. I jump into rails c:

Book.first
Book Load (0.1ms)  SELECT  "books".* FROM "books" ORDER BY "books"."id" ASC LIMIT ?  [["LIMIT", 1]]
=> #<Book id: 1>

Cool....now?

Page.count
(0.3ms)  SELECT COUNT(*) FROM "pages"
=> 3

Makes total sense. Next:

Book.first.destroy
Book Load (0.2ms)  SELECT  "books".* FROM "books" ORDER BY "books"."id" ASC LIMIT ?  [["LIMIT", 1]]
(0.1ms)  begin transaction
Page Load (0.1ms)  SELECT "pages".* FROM "pages" WHERE "pages"."book_id" = ?  [["book_id", 1]]
SQL (0.1ms)  DELETE FROM "pages" WHERE "pages"."id" = ?  [["id", 1]]
SQL (0.0ms)  DELETE FROM "pages" WHERE "pages"."id" = ?  [["id", 2]]
SQL (0.0ms)  DELETE FROM "pages" WHERE "pages"."id" = ?  [["id", 3]]
SQL (0.1ms)  DELETE FROM "books" WHERE "books"."id" = ?  [["id", 1]]

Yay! Almost there...after seeding again I do this:

Book.first.delete
Book Load (0.1ms)  SELECT  "books".* FROM "books" ORDER BY "books"."id" ASC LIMIT ?  [["LIMIT", 1]]
SQL (144.0ms)  DELETE FROM "books" WHERE "books"."id" = ?  [["id", 2]]

WTF?

Page.count
(0.1ms)  SELECT COUNT(*) FROM "pages"
=> 3

I know delete does not trigger callbacks, so that dependent: :destroy won't help me here. But the foreign key? Hello? I want referential integrity in my database level!! What am I doing wrong? I've tried more things, like moving the on_delete: :cascade to the field definition:

def change
  create_table :pages do |t|
    t.references :book, index: true, null: false
  end
end

But...nope, same result. I've searched and read the ActiveRecord documentation twice, and a few other questions in SO pointed me to my current setup (which is not the project I'm working on, but rather a newly generated one with the same basic configuration to replicate the error - yea, it fails there too), but I just can't put my finger on what's wrong. Perhaps it's just too late and I'm getting too tired. Help? Does Rails even support this? I'm using v5, far, far ahead of 4.2 where the constraints at db level were integrated. My db/schema.rb looks like this:

ActiveRecord::Schema.define(version: 20160218232358) do

  create_table "books", force: :cascade do |t|
  end

  create_table "pages", force: :cascade do |t|
    t.integer "book_id", null: false
    t.index ["book_id"], name: "index_pages_on_book_id"
  end

end

No trace of foreign keys?

Answer 1

For you're testing you are probably using SQLite, here only mysql, mysql2 and postgres are mentioned therefore I think rails does not support foreign keys on SQLite: http://edgeguides.rubyonrails.org/4_2_release_notes.html#foreign-key-support

It is also stated in another so post: https://stackoverflow.com/a/28801481/4560144