Apply Reference table to the Rails STI type/Polymorphic type/enum/inclusion validated columns
Tags: rails, ruby, sql
Published: 2020-06-28 07:19:49

---
title: Apply Reference table to the Rails STI type/Polymorphic type/enum/inclusion validated columns
published: true
description: 
tags: Rails, Ruby, SQL
//cover_image: https://direct_url_to_image.jpg
---

Applying Reference table[^reference_table] to the Rails application by following 2 steps.
[^reference_table]: https://en.wikipedia.org/wiki/Reference_table

1. Create reference tables that contains STI types / Polymorphic types / possible enum values / inclusion values.
2. Add foreign key constraint from the column to the reference table to prevent to set a wrong name to STI type / set a wrong name to Polymorphic type / set a invalid value to the enum / set a excluded values to inclusion validated column.

## Sample repository

https://github.com/hanachin/iikanji_enum/

## How to do

Set same type to a id column of a reference table and type column of a model table, or enum value column of the model table.
Then add foreign key constraint.

Sample migration file is looks like following:

```ruby
# db/migrate/20200627151958_create_posts.rb
class CreatePosts < ActiveRecord::Migration[6.0]
  def change
    create_table :posts do |t|
      t.string :type
      t.integer :state
      t.string :title
      t.text :body

      t.timestamps
    end

    create_table :post_states do |t|
      t.string :name

      t.timestamps
    end
    add_foreign_key :posts, :post_states, column: :state

    create_table :post_types, id: :string do |t|
      t.timestamps
    end
    add_foreign_key :posts, :post_types, column: :type
  end
end
```

After a `rails db:migrate`, the `db/schema.rb` will looks like following:

```ruby
# db/schema.rb
# This file is auto-generated from the current state of the database. Instead
# of editing this file, please use the migrations feature of Active Record to
# incrementally modify your database, and then regenerate this schema definition.
#
# This file is the source Rails uses to define your schema when running `rails
# db:schema:load`. When creating a new database, `rails db:schema:load` tends to
# be faster and is potentially less error prone than running all of your
# migrations from scratch. Old migrations may fail to apply correctly if those
# migrations use external dependencies or application code.
#
# It's strongly recommended that you check this file into your version control system.

ActiveRecord::Schema.define(version: 2020_06_27_160353) do

  # These are extensions that must be enabled in order to support this database
  enable_extension "plpgsql"

  create_table "post_states", force: :cascade do |t|
    t.string "name"
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
  end

  create_table "post_types", id: :string, force: :cascade do |t|
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
  end

  create_table "posts", force: :cascade do |t|
    t.string "type"
    t.integer "state"
    t.string "title"
    t.text "body"
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
  end

  add_foreign_key "posts", "post_states", column: "state"
  add_foreign_key "posts", "post_types", column: "type"
end
```

When there are STI models that uses `ActiveRecord::Enum` like following:

```ruby
# app/models/post.rb
class Post < ApplicationRecord
  enum state: { draft: 0, published: 1 }
end
```

```ruby
# app/models/draft_post.rb
class DraftPost < Post
end
```

```ruby
# app/models/published_post.rb
class PublishedPost < Post
end
```

Create reference table records that possible values of STI types / enum values like following:

```ruby
# app/models/post/state.rb
class Post < ApplicationRecord
  class State < ApplicationRecord
    class << self
      def seed
        Post.states.each do |state, id|
          find_or_create_by!(id: id, name: state)
        end
      end
    end
  end
end
```


```ruby
# app/models/post/type.rb
class Post < ApplicationRecord
  class Type < ApplicationRecord
    class << self
      def seed
        [PublishedPost, DraftPost].each do |klass|
          find_or_create_by!(id: klass.name)
        end
      end
    end
  end
end
```

Then run `rails runner Post::Type.seed` `rails runner Post::State.seed`.

Once reference tables and foreign key constraint set up, then you can see You can not save the model with type that does not exists on a reference table `post_types`.

```irb
Loading development environment (Rails 6.0.3.2)
irb(main):001:0> post = Post.first
  Post Load (0.2ms)  SELECT "posts".* FROM "posts" ORDER BY "posts"."id" ASC LIMIT $1  [["LIMIT", 1]]
irb(main):002:0> post
=> #<DraftPost id: 3, type: "DraftPost", state: "draft", title: "test", body: "test", created_at: "2020-06-27 16:14:49", updated_at: "2020-06-27 16:14:49">
irb(main):003:0> post.type = "YavayPost"
irb(main):004:0> post.save!
   (0.3ms)  BEGIN
  DraftPost Update (1.2ms)  UPDATE "posts" SET "type" = $1, "updated_at" = $2 WHERE "posts"."id" = $3  [["type", "YavayPost"], ["updated_at", "2020-06-27 16:40:40.492136"], ["id", 3]]
   (0.2ms)  ROLLBACK
Traceback (most recent call last):
        1: from (irb):4
ActiveRecord::InvalidForeignKey (PG::ForeignKeyViolation: ERROR:  insert or update on table "posts" violates foreign key constraint "fk_rails_43c128f7b9")
DETAIL:  Key (type)=(YavayPost) is not present in table "post_types".
irb(main):005:0>
```

The enum column is also the same, You can not save the enum value that does not exists on a reference table `post_states`.

```irb
Loading development environment (Rails 6.0.3.2)
irb(main):001:0> class Post; enum state: { amasawa: 4423 }; end
=> {:state=>{:amasawa=>4423}}
irb(main):002:0> post = Post.first
  Post Load (0.2ms)  SELECT "posts".* FROM "posts" ORDER BY "posts"."id" ASC LIMIT $1  [["LIMIT", 1]]
irb(main):003:0> post
=> #<DraftPost id: 3, type: "DraftPost", state: nil, title: "test", body: "test", created_at: "2020-06-27 16:14:49", updated_at: "2020-06-27 16:14:49">
irb(main):004:0> post.state = :amasawa
irb(main):005:0> post.save!
   (0.3ms)  BEGIN
  DraftPost Update (1.3ms)  UPDATE "posts" SET "state" = $1, "updated_at" = $2 WHERE "posts"."id" = $3  [["state", 4423], ["updated_at", "2020-06-27 16:43:03.201733"], ["id", 3]]
   (0.2ms)  ROLLBACK
Traceback (most recent call last):
        1: from (irb):5
ActiveRecord::InvalidForeignKey (PG::ForeignKeyViolation: ERROR:  insert or update on table "posts" violates foreign key constraint "fk_rails_93ccb3c476")
DETAIL:  Key (state)=(4423) is not present in table "post_states".
irb(main):006:0>
```

## Conclusion

Database constraint is awesome. Let's use reference table in the Rails apps.

You can apply the Reference table to STI types and possible enum values.
Also You can apply the Reference table to Polymorphic types  and valid inclusion values.