How do I create a unique index within a Rails create table migration?

Question

I'm using Rails 5 and PostgreSQL 9.5. How do I create a unique index within my table? I want to create the unique index out of two columns, which are themselves references to other tables. So I tried

class CreateUserNotificationsTable < ActiveRecord::Migration[5.0]
  def change
    create_table :user_notifications do |t|
      t.references :users, index: true, on_delete: :cascade
      t.references :crypto_currencies, index: true, on_delete: :cascade
      t.integer  "price",      null: false
      t.boolean "buy",      null: false
      t.index [:user_id, :crypto_currency_id], unique: true
    end
  end
end

but I'm getting the error

PG::UndefinedColumn: ERROR:  column "user_id" does not exist
: CREATE UNIQUE INDEX  "index_user_notifications_on_user_id_and_crypto_currency_id" ON "user_notifications"  ("user_id", "crypto_currency_id")
/Users/davea/.rvm/gems/ruby-2.4.0/gems/activerecord-5.0.4/lib/active_record/connection_adapters/postgresql/database_statements.rb:98:in `async_exec'
/Users/davea/.rvm/gems/ruby-2.4.0/gems/activerecord-5.0.4/lib/active_record/connection_adapters/postgresql/database_statements.rb:98:in `block in execute'
/Users/davea/.rvm/gems/ruby-2.4.0/gems/activerecord-5.0.4/lib/active_record/connection_adapters/abstract_adapter.rb:590:in `block in log'

What's the right way to create the unique index within the create table statement?

Qintar

Do you have user_id column persisted in user_notifications table? Check with UserNotification.column_names – Carincarina

I don't understand your question. I have this "t.references :users, index: true, on_delete: :cascade" just below "create table" so shouldn't that create a "user_id" column? – Qintar

When I run the above migration, it fails and no user_notifications table is created. I see the statements, "create_table(:user_notifications)" followed by "rake aborted! StandardError: An error has occurred, this and all later migrations canceled:" followed by the error I posted above. – Qintar

Answer 1

PG::UndefinedColumn: ERROR: column "user_id" does not exist

The problem is t.references :users creates a column called users_id not user_id, so it unable to create an index with t.index [:user_id, :crypto_currency_id], unique: true as the column user_id is not created which resulted in that error.

Solution:

Just change it to t.references :user. Same goes for t.references :crypto_currencies too.

class CreateUserNotificationsTable < ActiveRecord::Migration[5.0]
  def change
    create_table :user_notifications do |t|
      t.references :user, index: true, on_delete: :cascade
      t.references :crypto_currency, index: true, on_delete: :cascade
      t.integer  "price",      null: false
      t.boolean "buy",      null: false
      t.index [:user_id, :crypto_currency_id], unique: true
    end
  end
end

Carincarina

Answer 2

Try to extract index of create_table method, like:

class CreateUserNotificationsTable < ActiveRecord::Migration[5.0]
  def change
    create_table :user_notifications do |t|
      t.references :users, index: true, on_delete: :cascade
      t.references :crypto_currencies, index: true, on_delete: :cascade
      t.integer  "price",      null: false
      t.boolean "buy",      null: false
    end

    add_index :user_notifications, [:user_id, :crypto_currencies_id], unique: true
  end
end

Staal

Are you saying its impossible to create a table and include a unique index definition within the table creation block? – Qintar