How to add reference to a non primary key column of a table in rails5

Question

I am using Rails 5.0.1 for web development and deploying my app on Heroku. I am using postgreSQL as my database for Heroku and sqlite3 for my local development database.

I need to link to an account_number column of an Accounts table to two columns in a Transactions table. The account_number column is not the primary key of Accounts table.

And, the Transactions table has a column from_account, which I want to link to the account_number column in Accounts table and another column - to_account that I want to link to the same account_number column of Accounts table.

The account_number is not the primary key of accounts table but it is unique.

I am trying to do something like this in my migration file:

create_table :transactions do |t|
  t.string :from_account, foreign_key: true
  t.string :to_account, foreign_key: true
  t.timestamps
end

add_foreign_key :transactions, column: :from_account, :accounts, column: :account_number
add_foreign_key :transactions, column: :to_account, :accounts, column: :account_number

and my model file looks like this:

class Transaction < ApplicationRecord
  belongs_to :from_account, :foreign_key => 'from_account', :class_name => 'Account'
  belongs_to :to_account, :foreign_key => 'to_account', :class_name => 'Account'
end

But this gives error on both my local sqlite3 database and also on the Heroku's PostgreSQL database.

How do I model something like this in Rails5. So far all the tutorials I found online only tell how to link to the primary key of the referenced table.

EDIT: Maybe it is unclear from my question above, but the account_number field is already unique in the Accounts table in my database. This is the schema of my Accounts table:

create_table :accounts do |t|
  t.string :account_number, :unique => true
  # Other fields
  t.timestamps
end

Answer 1

Try primary_key:

class Transaction < ApplicationRecord
  belongs_to :from_account, :foreign_key => 'from_account', :class_name  => 'Account', :primary_key => 'account_number'
  belongs_to :to_account, :foreign_key => 'to_account', :class_name => 'Account', :primary_key => 'account_number'
end

Answer 2

As Arcana's answer says, give the referenced column via :primary_key:

class Transaction < ApplicationRecord
  belongs_to :from_account, :foreign_key => 'from_account', :class_name => 'Account',
    :primary_key => 'account_number'
  belongs_to :to_account, :foreign_key => 'to_account', :class_name => 'Account',
    :primary_key => 'account_number'
end

The language :primary_key in the belongs_to is misleading. It should really be :candidate_key or :references or :unique or primary_key_or_unique. But it isn't. It doesn't affect what the primary key in the referenced class is.

Active Record Associations

4 Detailed Association Reference
4.1 belongs_to Association Reference
4.1.2 Options for belongs_to
4.1.2.6 :primary_key

By convention, Rails assumes that the id column is used to hold the primary key of its tables. The :primary_key option allows you to specify a different column.

(In the relational model, a foreign key references a candidate key, which is some unique (not null) column set that doesn't contain a smaller unique (not null) column set, and is some set of columns you could have picked as primary key. But in (Active Record and) SQL a PRIMARY KEY actually declares a UNIQUE NOT NULL (that might contain a smaller UNIQUE NOT NULL) and a FOREIGN KEY REFERENCES a UNIQUE NOT NULL.)