Problems with foreign keys in rails

Question

It took some time to track this error, but I finally found out why. I am modeling a card game using the Rails framework. Currently, my database looks (basically) like this:

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

And now Rails ActiveRecord card.rb and game.rb 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 try to start a game and there are several games (more than 1), I get an 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)

, , cardid == id. , , - , Rails . cardsgames, , card_id id . , , . , "" , cardid gameid . , ( , Rails API , , .. ). "id" INSERT INTO . , , .

, ? - Rails, ? Rails? , , , , - Rail .

Answer 1

has_and_belongs_to_many , id.

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

. , , :

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

, Rails .

has_and_belongs_to_many 1: M . , Card :

has_and_belongs_to_many :players
has_and_belongs_to_many :games

"" "". Game:

has_one :rule

:class_name => Rule.

Answer 2

, .

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

Answer 3

. Dr. Nics

http://compositekeys.rubyforge.org/

Answer 4

. , "" . . - , , , . :

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 5

You might want to check out this foreign_key_migrations plugin