Foreign Key Issues in Rails

Question

Took me a while to track down this error but I finally found out why. I am modeling a card game using the Rails framework. Currently my database looks (mostly) like this:

cards     cards_games     games      
-----     -----------     -----
id        id              id
c_type    card_id         ...
value     game_id         other_stuff

And the Rails ActiveRecord card.rb and game.rb currently look like this

#card.rb
class Card < ActiveRecord::Base
  has_and_belongs_to_many :player
  has_and_belongs_to_many :game
  has_and_belongs_to_many :cardsInPlay, :class_name => "Rule"
end

---

#game.rb
class Game < ActiveRecord::Base
  has_and_belongs_to_many :cards
  has_many :players
  has_one :rules, :class_name => Rule
end

When I attempt to run a game and there are multiple games (more than 1), I get the error

ActiveRecord::StatementInvalid in GameController#start_game
# example
Mysql::Error: Duplicate entry '31' for key 1: INSERT INTO `cards_games` (`card_id`, `id`, `game_id`) VALUES (31, 31, 7)

Every time the action fails, cardid == id. This, I assume, has something with how Rails inserts the data into the database. Since there is no cardsgames object, I think it is just pulling card_id into id and inserting it into the database. This works fine until you have two games with the same card, which violates the primary key constraint on cardsgames. Being affluent with databases, my first solution to this problem was to try to force rails to follow a "real" definition of this relationship by dropping id and making cardid and gameid a primary key. It didn't work because the migration couldn't seem to handle having two primary keys (despite the Rails API saying that its okay to do it.. weird). Another solution for this is to omit the 'id' column in the INSERT INTO statement and let the database handle the auto increment. Unfortunately, I don't know how to do this either.

So, is there another work-around for this? Is there some nifty Rails trick that I just don't know? Or is this sort of structure not possible in Rails? This is really frustrating because I know what is wrong and I know several ways to fix it but due to the constraints of the Rail framework, I just cannot do it.

Answer 1

I found the solution after hacking my way through. I found out that you can use the "execute" function inside of a migration. This is infinitely useful and allowed me to put together an non-elegant solution to this problem. If anyone has a more elegant, more Rails-like solution, please let me know. Here's the solution in the form of a migration:

class Make < ActiveRecord::Migration
  def self.up
    drop_table :cards_games
    create_table :cards_games do |t|
      t.column :card_id, :integer, :null => false
      t.column :game_id, :integer, :null => false
    end
    execute "ALTER TABLE cards_games DROP COLUMN id"
    execute "ALTER TABLE cards_games ADD PRIMARY KEY (card_id, game_id)"

    drop_table :cards_players
    create_table :cards_players do |t|
      t.column :card_id, :integer, :null => false
      t.column :player_id, :integer, :null => false
    end
    execute "ALTER TABLE cards_players DROP COLUMN id"
    execute "ALTER TABLE cards_players ADD PRIMARY KEY (card_id, player_id)"

    drop_table :cards_rules
    create_table :cards_rules do |t|
      t.column :card_id, :integer, :null => false
      t.column :rule_id, :integer, :null => false
    end
    execute "ALTER TABLE cards_rules DROP COLUMN id"
    execute "ALTER TABLE cards_rules ADD PRIMARY KEY (card_id, rule_id)"
  end

  def self.down
    drop_table :cards_games
    create_table :cards_games do |t|
      t.column :card_id, :integer
      t.column :game_id, :integer
    end

    drop_table :cards_players
    create_table :cards_players do |t|
      t.column :card_id, :integer
      t.column :player_id, :integer
    end

    drop_table :cards_rules
    create_table :cards_rules do |t|
      t.column :card_id, :integer
      t.column :rule_id, :integer
    end
  end
end

Answer 2

You might want to check out this foreign_key_migrations plugin

Answer 3

See Dr. Nics composite primary keys

http://compositekeys.rubyforge.org/

Answer 4

To drop the ID column, simply don't create it to begin with.

  create_table :cards_rules, :id => false do ...

Answer 5

has_and_belongs_to_many implies a join table, which must not have an id primary key column. Change your migration to

create_table :cards_games, :id => false do ...

as pointed out by Matt. If you will only sleep better if you make a key from the two columns, create a unique index on them:

add_index :cards_games, [ :card_id, :game_id ], :unique => true

Additionally, your naming deviates from Rails convention and will make your code a little harder to read.

has_and_belongs_to_many defines a 1:M relationship when looking at an instance of a class. So in Card, you should be using:

has_and_belongs_to_many :players
has_and_belongs_to_many :games

Note plural "players" and "games". Similarly in Game:

has_one :rule

This will let you drop the unnecessary :class_name => Rule, too.