PigRider Blogs

Sign In
HTML CSS Javascript Ruby on Rails C++ Java Python SQL Git Linux Others All

How to Set Primary Keys Other Than :id in Rails 3 with MySQL

[Author: Dingyu]   [Sun, 2013-08-11, 17:43]   [3769 views]

Ruby on Rails SQL

In Rails 3, the primary key in a database table is very important. Rails model ActiveRecord functions, like save, destroy, update_attributes, and so on, require it to invoke callbacks. Rails 3 uses the column 'id' as a default primary key in a database table. However, in MySQL, only the primary key is clustered. So sometime, using a primary key other than :id in MySQL may make things much easier. This article lists two examples that use primary keys other than :id in Rails 3 with MySQL.


Example 1: set the column 'name' as a primary key in the MySQL table, but use the column 'restaurantID' as a Rails primary key for Rails model ActiveRecord functions.

In the migrate file:
class CreateRestaurantInfos < ActiveRecord::Migration
  def up
    create_table :restaurant_infos, :id=>false do |t|
      t.integer :restaurantID, :limit=>11, :null=>false
      t.string :name, :limit=>50, :null=>false
      t.timestamps
    end

    execute "ALTER TABLE restaurant_infos ADD PRIMARY KEY (name)"
    add_index :restaurant_infos, [:restaurantID], :unique=>true
    execute "ALTER TABLE restaurant_infos MODIFY COLUMN restaurantID INT(11) AUTO_INCREMENT"
  end

  
  def down
    execute "ALTER TABLE restaurant_infos MODIFY COLUMN restaurantID INT(11)"
    remove_index :restaurant_infos, :column=>[:restaurantID]
    execute "ALTER TABLE restaurant_infos DROP PRIMARY KEY"
    drop_table :restaurant_infos   
  end
end
In the model file:
class RestaurantInfo < ActiveRecord::Base
  attr_accessible :name, :restaurantID
  self.primary_key="restaurantID"
end
Example 2: set two columns 'restaurantID' and 'dishName' as composite primary keys in the MySQL table, but use the column 'dishID' as a Rails primary key for Rails model ActiveRecord functions. In the migrate file:
class CreateRestaurantMenus < ActiveRecord::Migration
  def up
    create_table :restaurant_menus, :id=>false do |t|
      t.integer :dishID, :limit=>11
      t.integer :restaurantID, :limit=>11, :null=>false
      t.string :dishName, :limit=>20, :null=>false
      t.timestamps
    end

    execute "ALTER TABLE restaurant_menus ADD PRIMARY KEY (restaurantID,dishName)"
    add_index :restaurant_menus, [:dishID], :unique=>true
    execute "ALTER TABLE restaurant_menus MODIFY COLUMN dishID INT(11) AUTO_INCREMENT"
  end


  def down
    execute "ALTER TABLE restaurant_menus MODIFY COLUMN dishID INT(11)"
    remove_index :restaurant_menus, :column=>[:dishID]
    execute "ALTER TABLE restaurant_menus DROP PRIMARY KEY"
  end
end
In the model file:
class RestaurantMenu < ActiveRecord::Base
  attr_accessible :dishID, :dishName, :restaurantID
  self.primary_key="dishID"
end