Unique foreign key in rails migration

Question

In migrations file, I'm adding foreign key using below syntax.

class CreatePreferences < ActiveRecord::Migration
  def change
    create_table :preferences do |t|
      t.integer :user_id, null: false
      t.timestamps null: false
    end
    add_foreign_key :preferences, :users, column: :user_id, dependent: :destroy, :unique => true
  end
end

But :unique => true is not working.

mysql> show indexes from preferences;
+-------------+------------+---------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table       | Non_unique | Key_name            | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+---------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| preferences |          0 | PRIMARY             |            1 | id           | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| preferences |          1 | fk_rails_87f1c9c7bd |            1 | user_id      | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------------+------------+---------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

What is the right way to add a unique foreign key using migrations?

I could've added uniqueness validations in the model itself. But it won't be foolproof if I have multiple workers running. (Reference)

Janson

I guess you should add index to make it unique in the DB level – Hierophant

Answer 1

class CreatePreferences < ActiveRecord::Migration
  def change
    create_table :preferences do |t|
      t.integer :user_id, null: false
      t.timestamps null: false
    end
    add_foreign_key :preferences, :users, column: :user_id, dependent: :destroy, :unique => true
    add_index :preferences, :user_id, unique: true
  end
end

Cutoff

Do I need to have unique in both add_foreign_key and add_index ? – Janson

foreign_key and index are different things, so I think you can have both unique – Cutoff

As of Rails 5.2, unique is not a valid option for add_foreign_key(), and is silently ignored. It is however a valid option for add_index(). – Staphylococcus

Answer 2

In Rails 5 you can accomplish the same with the more elegant:

t.belongs_to :user, foreign_key: true, index: { unique: true }

This will generate a foreign key constraint as well as a unique index. Don't forget to add null: false if this is a required association (sounds like it in the case of the original question).

Note that belongs_to is just an alias for references.

Togetherness