Rails 5.1 & Postgres - adding unique constraint on two columns, when one is a foreign key

Question

So I read this question, answer and the comments, but it doesn't answer my case, which is what to do when of the columns is a foreign key?

Here is my original migration to create the table in question:

class CreateTemplates < ActiveRecord::Migration[5.1]
  def change
    create_table :templates, id: :uuid do |t|
      t.references :account, type: :uuid, foreign_key: true
      t.string :name
      t.text :info
      t.string :title

      t.timestamps
    end
  end
end

Since account_id is a foreign_key (and identifies the customer) it will appear in almost all (99%) of queries on this table.

Now it has been decided that name should be unique to account, so the model has been updated:

  validates_uniqueness_of :name, scope: [:account]

So once I add the joint index:

add_index :templates, [:name, :account_id], unique: true

should I delete the index on account_id?

I ask because in SQLLite (see this), it seems the answer would be that I don't need the single index on account_id and to create my new index with account_id in the first position:

add_index :templates, [:account_id, :name], unique: true

I'm using postgres, so does the same idea apply?

Sean Magyar · Accepted Answer · 2017-11-22 20:51:18Z

5

You have to add extra index if it's not the first index.

So if you have this:

add_index :templates, [:name, :account_id], unique: true

then you should not delete the original :account_id foreign key index, since it is second index.

I recommend you to read about index implementations. It's pretty interesting and you can learn a lot from it.

answered Nov 22, 2017 at 20:51

Sean Magyar

2,3801 gold badge27 silver badges59 bronze badges

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

2 Comments

rmcsharry Over a year ago

So you are saying that Postgres works the same way as the SQLite example I linked to. Which means I could delete the account_id foreign_key index and create a new composite with account_id as the first index...and that would provide a little better performance on inserts because they the db only has to update one index, right?

Sean Magyar Over a year ago

You could do that but it depends how your code uses it. If you first search based on the account_id then based on the name everywhere in your code, then yes you could delete it.

Collectives™ on Stack Overflow

Rails 5.1 & Postgres - adding unique constraint on two columns, when one is a foreign key

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related