index: true vs foreign_key: true (Rails)

Question

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?

Sonnysonobuoy

#22815509 possibly this will help – Poliomyelitis

Answer 1

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

Benildas

Answer 2

Index improve speed of data retrieval operations on database tables. When we write index: true to any column, it adds a database index to this column. For example I was creating a table:

    create_table :appointments do |t|
      t.references :student, index: true 
    end

It will create student_id column in appointments table.

A foreign key have different use case, it is a relationship between tables. It allow us to declare an index in one table that is related to an index in another table and also some constraints are placed.The database enforces the rules of this relationship to maintain referential integrity. For example we have two table profiles and educations, and a profile may have many educations.

create_table :educations do |t| 
  t.belongs_to :profile, index: true, foreign_key: true
end

Now we have profile_id column in educations table which is foreign key of profiles table. It prevents a record from being entered into the educations table unless it contains a profile_id value that exists in the profiles table. So referential integrity will be maintained.

Perdurable

Woudn't t.references :student create student_id column in appointments table? If so, what does index: true do? – Sonnysonobuoy

index: true does indexing here. – Perdurable

You don't need to add an index with references - it's added by default. https://mcmap.net/q/501866/-do-i-need-to-manually-add-indexes-to-my-foreign-key-fields-or-does-rails-do-it-automatically – Theodore