Double primary key indexes with Rails and PostgreSQL

Question

I'm using UUID as primary key in my tables, created in this way:

Migration:

class CreateAccounts < ActiveRecord::Migration
  def change
    create_table :accounts, :id => false do |t|
      t.uuid :uuid, :null => false
      t.timestamps
    end
    execute "ALTER TABLE accounts ADD PRIMARY KEY (uuid);"
    add_index :accounts, :uuid, :unique => true
  end
end

Model:

class Account < ActiveRecord::Base  
    primary_key = :uuid
    base.default_value_for(:uuid, :allows_nil => false) { UUIDTools::UUID.random_create.to_s }
end

But I now see that I have double indexes on the primary key UUID column:

Double indexes on the primary key

(1) I guess this comes from ADD PRIMARY KEY which creates the "XXX_pkey" index, and I can safely remove add_index :accounts, :uuid, :unique => true in this migration and from the running database?

(2) I can see that only the manually added index is used, and not the automatically added. Is this by random?

(3) Which of these indexes should be removed and what is the best way to do that in production through Rails' migrations?

user330315 · Accepted Answer · 2013-02-10 15:05:39Z

1

A column defined as the primary key is (by definition) unique. PostgreSQL (actually every DBMS) will automatically create an unique index to ensure this.

So there is no need need to create an additional unique index manually.

So you should remove the one that you created manually (I don't know Ruby, in plain SQL this would as easy as drop index ...)

answered Feb 10, 2013 at 15:05

user330315

Sign up to request clarification or add additional context in comments.

Comments

MiGro · Accepted Answer · 2013-02-05 10:51:49Z

1

They are not primary indexes, how have one index primary (added by execute) and one unique (added by add_index). There is a difference between primary key and unique index. Primary key does not accept NULL where unique does.. If you need to remove the index just create a migration which will remove the index:

remove_index(table_name, :column => column_name): Removes the index specified by column_name.

Source: http://api.rubyonrails.org/classes/ActiveRecord/Migration.html

answered Feb 5, 2013 at 10:51

MiGro

1,51111 silver badges9 bronze badges

Collectives™ on Stack Overflow

Double primary key indexes with Rails and PostgreSQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related