CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3postgresqlrails-activerecord
thejaz
thejaz

Reputation: 2773

Double primary key indexes with Rails and PostgreSQL

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?

Upvotes: 1

Views: 628

Answers (2)

user330315
user330315

Reputation:

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 ...)

Upvotes: 1

MiGro
MiGro

Reputation: 1511

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

Upvotes: 1

Related Questions