CODEWITHSUNDEEP
ruby-on-railsrails-activerecord
Adam Zerner
Adam Zerner

Reputation: 19178

Rails ActiveRecord two fields each referencing the same table

I have a Player class that I want to have high_school_team and club_team properties. So then I figure Player will have high_school_team_id and club_team_id properties that point to the corresponding team. I try to do this in the following migration, but it doesn't work.

class CreatePlayers < ActiveRecord::Migration[6.0]
  def change
    create_table :players do |t|
      t.string :first_name
      t.string :middle_name
      t.string :last_name
      t.decimal :height
      t.decimal :weight
      t.date :birthday
      t.references :team, :high_school_team, foreign_key: true
      t.references :team, :club_team, foreign_key: true
      t.decimal :gpa
      t.string :class_year
      t.string :intended_major
      t.string :email
      t.string :phone_number
      t.text :notes

      t.timestamps
    end
  end
end

It gives the following error:

code/scout-db [master●] » rails db:migrate
== 20191218003854 CreatePlayers: migrating ====================================
-- create_table(:players)
rails aborted!
StandardError: An error has occurred, this and all later migrations canceled:

you can't define an already defined column 'team_id'.
/Library/Ruby/Gems/2.6.0/gems/activerecord-6.0.2.1/lib/active_record/connection_adapters/abstract/schema_definitions.rb:372:in `column'
...

HighSchoolTeam and ClubTeam are models that do single table inheritance with Team.

I don't see why I'm getting the error. The docs seem to say that the first argument for t.referenes is table_name and the second ref_name. :team is the name of the table and I want the references to be high_school_team_id and club_team_id.

When I switch the order of the arguments to t.references, it still doesn't work. Somehow it gives the same error: you can't define an already defined column 'team_id'..

Upvotes: 0

Views: 956

Answers (2)

max
max

Reputation: 101821

It looks you you are confusing SchemaStatement#add_reference and TableDefinition#references which have completely different signatures.

If you want to setup a foreign key column where the table can't be derived from the name of the column (the first argument) you just pass foreign_key: { to_table: :teams}.

class CreatePlayers < ActiveRecord::Migration[6.0]
  def change
    create_table :players do |t|
      t.references :high_school_team, foreign_key: { to_table: :teams}
      t.references :club_team, foreign_key: { to_table: :teams}
    end
  end
end

t.references :high_school_team, index: true as recommended by the other answers is NOT equivilent. That just adds a index to the column but no foreign key constraint.

You can then setup the assocations on Player as:

class Player < ApplicationRecord
  belongs_to :high_school_team, class_name: 'Team'
  belongs_to :club_team, class_name: 'Team'
end

You can't use a single has_many :players assocation on the other end though as the foreign key column can be either players.high_school_team_id or players.club_team_id.

class Team
  has_many :high_school_team_players, 
     foreign_key: :high_school_team_id,
     class_name: 'Player'
  has_many :club_team_players, 
     foreign_key: :club_team_id,
     class_name: 'Player'
end

But really a better alternative in the first place would have been to setup a join table:

class Player
  has_many :placements
  has_one :high_school_team, 
    through: :placements,
    source: :team
    class_name: 'Team'
  has_one :club_team, 
    through: :placements,
    source: :team
    class_name: 'Team'
end

class Placement
  belongs_to :player
  belongs_to :team
end

class Team
  has_many :placements
  has_many :players, through: :placements  
end

Upvotes: 2

Akshay Goyal
Akshay Goyal

Reputation: 925

The doc you mentioned talks about the case when you need to add reference to an existing table.

For adding a refernence to a new table:

t.references :team, :high_school_team, foreign_key: true

This piece of code is wrong. Instead, it should be

t.references :high_school_team, foreign_key: {to_table: :teams}

to_table is needed to add database referential integrity

So your migration will be like this:

class CreatePlayers < ActiveRecord::Migration[6.0]
  def change
    create_table :players do |t|
      ....
      t.references :high_school_team, foreign_key: {to_table: :teams}
      t.references :club_team, foreign_key: {to_table: :teams}
      ....
    end
  end
end

Upvotes: 0

Related Questions