24

What is the difference between "add_foreign_key" and "add_reference" methods in rails?

According to rails official guide all I understand is that they both are used to create foreign key constraint between two tables.

Masa Sakano
  • 1,921
  • 20
  • 32
Pradhumn Sharma
  • 1,663
  • 2
  • 10
  • 19

3 Answers3

25

add_foreign_key - adds a new foreign key. from_table is the table with the key column, to_table contains the referenced primary key.

add_reference - is meant as a shortcut for creating a column, index and foreign key at the same time.

What is foreign key - a foreign key is a field or group of fields in a table that uniquely identifies a row in another table.

Roman Kiselenko
  • 43,210
  • 9
  • 91
  • 103
  • 4
    More than that, a foreign key is a way to maintain referential integrity. If you have a User that depends on a Location, you would want to prevent the deletion of that Location as long as Users depend on it. A foreign key is the way to do that. – mpowered Apr 10 '19 at 17:31
  • Looks like (in Rails 7 at least) that the `foreign_key` option to `add_reference` defaults to false, pass true to add. https://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/SchemaStatements.html#method-i-add_reference – pixelearth Mar 23 '23 at 18:54
19

(Note: This answer is based on Rails 6.0.)

In a word, add_reference (Ref) is kind of a short-form of a combined set of add_column, add_index, and add_foreign_key (Ref) without adding a DB-level foreign key in default. So, when you want to achieve something simple enough or (conversely?) a polymorphic reference, add_reference is handy. If not, use add_foreign_key, maybe combined with explicit add_index.

As a simple example, these two are (I think) equivalent to each other:

add_reference   :articles, :author, foreign_key: true

add_column      :articles, :author_id, :bigint, null: true
add_foreign_key :articles, :authors
add_index       :articles, :author_id

Here are more detailed differences:

  1. The second argument of add_reference is a reference (column name without _id, hence usually singular), whereas that of add_foreign_key is a table name (hence usually plural).
  2. In add_reference,
    1. DB-level foreign key is not created in default, unless foreign_key option is specified non-nil.
    2. index: true is default, whereas the index is irrelevant in add_foreign_key
    3. null: true is default (allowing nulls for the column), which is irrelevant in add_foreign_key
  3. polymorphic: true is available only with add_reference in Rails (which will create 2 columns in one action; see Ref).
  4. The formats of the accepted options between the two are totally different, though add_reference is largely more inclusive, accepting a wider range of options.

Two example realistic use-cases

For the has_one association, where null is forbidden:

add_reference :products, :merchant, null: false, index: {unique: true}, foreign_key: {on_delete: :cascade}

When a table has 2 foreign-key columns to an identical table:

add_foreign_key :products, :merchants, column: :seller_id
add_foreign_key :products, :merchants, column: :buyer_id
add_index :products, [:seller_id, :buyer_id], unique: true, name: 'index_my_name_shorter_than_64chars'
eux
  • 3,072
  • 5
  • 14
Masa Sakano
  • 1,921
  • 20
  • 32
  • 4
    Thank you so much for this answer. I've spent so long searching for an answer that touches on all these things. – seeker Mar 17 '21 at 08:10
0

There is a limitation to add_reference compared to add_foreign_key.

As I am curious if there is a way to do the exact following thing with add_reference. Afaik the standard foreign_key to primary_key/reference_key mapping can not be diverged with add_reference.

Migration snippet

add_foreign_key :foos, :bars, column: :foo_key, primary_key: :foo_key, type: :string 
add_index :foos

Usecase is when trying to map the foreign_key to the primary_key in a non Standard way. Let's say using a table with STI to hold multiple references

Zili Huang
  • 1