ruby-on-railsforeign-keysruby-on-rails-5rails-activerecordrails-migrations

index: true vs foreign_key: true (Rails)


Following a guide, I ran the following command:

rails g migration CreateSnippetsUsers snippet:belongs_to user:belongs_to

This created the following migration:

class CreateSnippetsUsers < ActiveRecord::Migration[5.0]
  def change
    create_table :snippets_users do |t|
      t.belongs_to :snippet, foreign_key: true
      t.belongs_to :user, foreign_key: true
    end
  end
end

In the past I've seen the same thing, but with index: true instead of foreign_key: true. What's the difference between the two?


Solution

  • Indexes, foreign keys and foreign keys constraints are strictly related concepts in databases that are often confused or misunderstood.

    REFERENCES
    When you declare a reference, you're simply saying to include a column whose values should match those of another table (and in Rails you also get some useful methods to navigate through the associated models). In the example:

    create_table :appointments do |t|
      t.references :student
    end
    

    the appointments table will have a column named student_id whose values should be in the pool of students' id values.

    INDEXES
    Since when you add a reference you will probably use that column often, you may (and probably should!) also tell you database to boost the look up speed using the reference column. You can do this with the option index: true (which by the way is a default option in the reference method since Rails 5). Indexes have few drawbacks, the main being a larger memory consumption.

    FOREIGN KEY CONSTRAINTS
    From what said so far, reference column and foreign column are synonyms. But do you remember when I said that a reference column's values should match those of another table? If you simply declare a reference, it's your responsibility to ensure that a matching row on the referenced table exists, or someone will end up doing nonsense actions like creating appointments for non-existing students. This is an example of database integrity, and fortunately there are some mechanisms that will grant a stronger level of integrity. These mechanisms are called ' database constraints'. What the option foreign_key: true does is exactly to add this kind of constraint on the reference column, to reject any entry whose foreign key values are not in the referenced table.

    Database integrity is a complex task, growing in difficulty with the database's complexity. You probably should add also other kind of constraints, like using they keywords dependent: :destroy in your class to ensure that when you delete a student, all of its existing appointments are also destroyed.

    As usual, here's a RTFM link: https://guides.rubyonrails.org/association_basics.html