How to Implement Composite Primary Keys in Rails

How to implement composite primary keys in rails

Add an index.

In rails, everything works better if you have id for the primary key.

It is possible for you to buck the system... but it's better not to unless you really know what you're doing - and if you're asking what the difference is between an index and a key then you don't... which is cool BTW, you don't need to know that in order to do well with Rails... but it really helps if you're going to be changing something fundamental. Because using anything other than id as a primary key is harder. Things break more. You will have to fix it without understanding why they're breaking and why you'd have needed them in the first place...

There is nothing wrong with having id as a primary key and also having a constraint that makes sure you have a unique organisation_id+department_id...

Note: I have made the assumption you don't know the difference between an index/primary-key - this assumption might not hold, but be an artifact of the way you asked the question... if so my apologies. :)
In that case... the difference is that Rails does all kinds of nice magic for you if you just use what it expects... and is a PITA if you don't.

Otherwise...
a primary-key is a uniquely-identifying bit of information. You might think that org_id/dept_id never changes... but you'd be surprised how often real-world data changes in real life... and how much of a pain it can be to update your entire db's worth of relations when it does...

A unique index (OTOH) nicely constrains the data in the way you want... without having that hassle of having to update stuff if somebody decides department 42 must be department 23 now.
Additionally indexes let you look up data by that column-pair much quicker than doing a row-scan of the entire db would.

Composite primary keys in ruby on rails

The following will work I think.

require 'composite_primary_keys'
class StringProperty < ActiveRecord::Base
    self.primary_keys = :entity_id, :property_id
    set_table_name "problem.string_property"
    attr_accessible :entity_id, :property_id, :value
end

How to add composite primary keys in db using Rails?

Since nobody have posted anything useful, I'll share what I've got by now.

Gem 'composite_primary_key' (CPK) doesn't change anything in database, so if you still want it - you should do it manually (add the command to migration).

The only thing CPK does is extending Rails with 'understanding' what a composite primary key is, since it originally doesn't have and shows an error.

Anyway I found it is very sohpisticated and problematic to use composite primary keys in Rails apps because it makes using other gems quite nerveous (with every new gem installation you keep in mind that somewhere something can go wrong) as well as changing your code with cpks in future. Also it makes your code harder to understand for other people who are not familliar with this feature. So you should always specify that you have used CPK.

A very good alternative is adding an index with 'unique' option which technically means quite the same but doesn't require additional headache.

So use it only if you really have to and there's no other less sophisticated solution!

Is it possible to define composite primary key for table using active record?

There are some gems that give you this ability, such as composite_primary_key.

I don't know if Rails 4 has added support for this, would be very interested to hear if it does.

Refactoring composite primary key to simple key for Rails?

You're very likely overthinking and overcomplicating the problem. Stick with the AR idiom of using a single primary key named id.

For join tables use foreign keys instead of compound PKs. Also stick to the naming conventions unless you want to look inept or annoy other devs by violating the principle of least surprise. That means:

• use snake_case for everything (table names, columns, index names etc.)
• don't prefix the column with the table name. It just makes every variable in your application longer and is not needed in a ORM.
• use _id for foreign key columns. ex; parent_id
• use _at for timestamps. ex; confirmed_at
• use thing_other_things for join tables unless there is a more descriptive name

Also many of these cases should just use an indirect relation to join up the hierarchy instead of duplicating foreign keys.

This is an example DB schema:

ActiveRecord::Schema.define(version: 20161214013752) do

  # These are extensions that must be enabled in order to support this database
  enable_extension "plpgsql"

  create_table "ingredient_types", force: :cascade do |t|
    t.string   "name"
    t.string   "description"
    t.datetime "created_at",  null: false
    t.datetime "updated_at",  null: false
  end

  create_table "ingredients", force: :cascade do |t|
    t.integer  "ingredient_type_id"
    t.datetime "created_at",         null: false
    t.datetime "updated_at",         null: false
    t.index ["ingredient_type_id"], name: "index_ingredients_on_ingredient_type_id", using: :btree
  end

  create_table "recipe_ingredients", force: :cascade do |t|
    t.integer  "recipe_id"
    t.integer  "ingredient_id"
    t.float    "quantity"
    t.datetime "created_at",    null: false
    t.datetime "updated_at",    null: false
    t.index ["ingredient_id"], name: "index_recipe_ingredients_on_ingredient_id", using: :btree
    t.index ["recipe_id"], name: "index_recipe_ingredients_on_recipe_id", using: :btree
  end

  create_table "steps", force: :cascade do |t|
    t.integer  "recipe_id"
    t.integer  "ordinal"
    t.text     "instruction"
    t.datetime "created_at",   null: false
    t.datetime "updated_at",   null: false
    t.index ["recipe_id"], name: "index_steps_on_recipe_id", using: :btree
  end

  create_table "recipes", force: :cascade do |t|
    t.string   "name"
    t.string   "description"
    t.datetime "created_at",  null: false
    t.datetime "updated_at",  null: false
  end

  add_foreign_key "ingredients", "ingredient_types"
  add_foreign_key "recipe_ingredients", "ingredients"
  add_foreign_key "recipe_ingredients", "recipes"
  add_foreign_key "steps", "recipes"
end

---

class IngredientType < ApplicationRecord
  has_many :ingredients
end

class Ingredient < ApplicationRecord
  belongs_to :ingredient_type
  has_many :recipe_ingredients
  has_many :recipes, through: :recipe_ingredients
end

class RecipeIngredient < ApplicationRecord
  belongs_to :recipe
  belongs_to :ingredient
  has_one :ingredient_type, through: :ingredient
end

class Step < ApplicationRecord
  belongs_to :recipe
end

class Recipe < ApplicationRecord
  has_many :recipe_ingredients
  has_many :ingredients, through: :recipe_ingredients
  has_many :steps
end

How can I set a composite primary key in my migration file in Ruby on Rails?

My colleague gave me his answer and I think is't right, thanks for Sachin R & Matt any way:

create_table :my_records, id: false do |t|
  t.integer :partner_id, references: [:Partner, :partnerID]
  t.integer :client_id, references: [:Client, :id]
end

Define a unique primary key based on 2 columns

add_index :words, ["id", "language_id"], :unique => true

It should work. Maybe you have already some non-unique data in your db and index can't be created? But (as @Doon noticed it will be redundant since id is always unique). So you need create primary key on two columns.

To define 2 column primary key in rails use:

create_table :words, {:id => false} do |t|
  t.integer :id
  t.integer :language_id
  t.string :value
  t.timestamps
end
execute "ALTER TABLE words ADD PRIMARY KEY (id,language_id);"

And set primary_key in your model with this gem: http://rubygems.org/gems/composite_primary_keys:

class Word < ActiveRecord::Base
    self.primary_keys = :id,:language_id
end

---

Related Topics