4

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
ali
  • 846
  • 2
  • 18
  • 34
Sumit
  • 2,189
  • 7
  • 32
  • 50
  • Yes, the account_number is unique. Also could you please elaborate a bit about the "refer to this via a Foreign Key" part? How exactly do I do that, other than the way I have already done as mentioned in the question? – Sumit Feb 22 '17 at 22:45
  • I don't know RoR (don't need to, don't want to) In DDL it is simple the *target* for the FK should be at least UNIQUE (possibly a Primary Key), like I commented earlier. So , in the definition for "Transaction" : `... from_account INTEGER REFERENCES "Account" (Account_number) ...` Similar for to_account, of course. BTW: `Transaction` is a bad name for a table, it is also a keyword in SQL. – wildplasser Feb 22 '17 at 22:55
  • Also, now I see `t.string :from_account,` which probably means that it is a varchar or text column. Which is a bad choice, because character columns are *heavier* than plain ints. I'd prefer a FK to a surrogate column (probably your PK) instead. (but keep the UNIQUE constraint on the text column, of course) – wildplasser Feb 23 '17 at 00:25
  • from_account INTEGER REFERENCES "Account" (Account_number) doesn't work on RoR / PostgreSQL. and thanks for suggesting that Transaction is a keyword I guess I should change that. – Sumit Feb 23 '17 at 00:27
  • Thanks for trying to help, I found the solution to my problem in another answer below. – Sumit Feb 23 '17 at 01:02

3 Answers3

7

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
archana
  • 1,282
  • 8
  • 11
  • would using :primary_key => 'account_number' make the account_number column the primary key of account table. Also, is there no way to reference a column which is just a unique column, but not the primary key – Sumit Feb 23 '17 at 00:29
  • Thanks Archana, this solution worked both on PostgreSQL and sqlite3. and I still have the account_number masked as just unique in Accounts table. I am still not sure whether the changes you suggested made the account_number the primary key now or not. – Sumit Feb 23 '17 at 00:58
  • 2
    @sumit No, the :primary_key doesn't affect any primary keys. It's misnamed; see my answer. – philipxy Feb 23 '17 at 05:08
  • is it applicable only to rails 5 or would work with rails 4.2 – Rpant May 18 '18 at 02:02
3

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.)

Community
  • 1
  • 1
philipxy
  • 14,867
  • 6
  • 39
  • 83
0

I'd guess that it's caused by either not specifying a primary key during your migration or within your model

Community
  • 1
  • 1
SomeSchmo
  • 665
  • 3
  • 18