Discard composite primary keys in Rails

Most people might not do this, but some try to use composite primary keys in Rails application.

Yes, I met this situation. And they used composite_primary_keys gem.

But this is a bit risky to use this type of gem, it overrides ActiveRecord and no one knows when the maintenance of the gem ends.

I talked about this to the developer team and replaced it with normal id primary key. Though this was not so difficult one, I'll write about the detail in this post.

Steps

1. Replace ActiveRecord::Base#{destroy,delete},.destroy_all with .delete_all
2. Add proper index
3. Drop composite primary keys and add id column instead

Use .delete_all

As an example, you have ActiveRecord::Base class PostTag which has composite primary keys for post_id and tag_id.

class PostTag < ApplicationRecord
  # this is a composite_primary_keys method
  self.primary_keys = [:post_id, :tag_id]

  belongs_to :post # Post class
  belongs_to :tag  # Tag class
end

The following ActiveRecord::Base methods try to delete record by id if you don't specify .primary_keys= in the model:

• #destroy
• #delete
• .destroy_all

So, you should replace all of them with .delete_all.

It must affect nothing with current code because the records are unique by each keys.

# before
post_tag.destroy

# after
PostTag.where(post: post_tag.post, tag: post_tag.tag).delete_all

# or you can just overrides method in PostTag class definition
def destroy
  self.class.where(post: post, tag: tag).delete_all
end

Add proper index

If you PostTag table definition is like following:

create_table "post_tags", primary_key: ["post_id", "tag_id"]  do |t|
  t.bigint "post_id", null: false
  t.bigint "tag_id", null: false
  t.index ["tag_id"], name: "tag_id"
end

You cannot add id as primary key, then add indices to all primary keys. In this example, add index for post_id.

You also add unique index for a pair of post_id and tag_id.

Drop current composite primary keys and add id as primary key

This is the final step. Run the following query for the table.

ALTER TABLE post_tags
  DROP PRIMARY KEY,
  ADD id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

In this query, I adds:

• type bigint
• unsigned
• not null constraint
• auto_increment
• insert in the first order in the table definition

You can change each of them if you want.