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.
- Add proper index
- Drop composite primary keys and add
As an example, you have
PostTag which has composite primary keys for
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
ActiveRecord::Base methods try to delete record by
id if you don’t specify
.primary_keys= in the model:
So, you should replace all of them with
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
You also add unique index for a pair of
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
- not null constraint
- insert in the first order in the table definition
You can change each of them if you want.