CODEWITHSUNDEEP
ruby-on-railspostgresqlcomposite-key
banditKing
banditKing

Reputation: 9579

Rails: Creating a composite key for my postgresql db

I added a migration to change the primary key of my existing db from the autogenerated system one to a composite key based on the following command:

add_index :listings, [:telephone, :name, :latitude, :longitude] , :unique=>true

My questions are:

  1. the index generated by the command gonna be an integer? Since all the parameter fields are strings.

  2. if I run this command after the db was created, does it regenerate the keys of all the rows or do I have to run a separate command for that?

  3. does it make a difference when this command is run? If there were no rows in the table when this was run as opposed to running this after the table has rows?

Thanks

EDITED after reading the post below:====================================================

This is my model:

# Table name: listings
#
#  id                :integer         not null, primary key
#  name              :string(50)      default("ERROR:Not Available"), not null
#  telephone         :string(15)      default("ERROR:Not Available"), not null
#  latitude          :string(15)      default("ERROR:Not Available"), not null
#  longitude         :string(15)      default("ERROR:Not Available"), not null
#  avatar           :string(30)      default("Not Available")

So as you can see the primary key is the id, autogenerated by the system When I added the command above what exactly does that do then? I thought it changed the id field so that its an integer generated based on the 4 column values.

As you can guess, there can be mor than 2 people with the same names. So the only true way to guarantee uniqueness here is to create some sort of index based on the 4 columns.

By using the command above, am I doing the right thing? Will that work to speed up queries?

Upvotes: 0

Views: 860

Answers (1)

Ismael
Ismael

Reputation: 16720

  1. That doesn't change your primary key. That only creates and index, used for increasing query performance (if used correctly)
  2. Index has no type, it's like a map, not a column.
  3. Seems Rails does not support composite keys.

Maybe a solution for you: http://compositekeys.rubyforge.org/

EDIT: If your only concern is about uniqueness you should be aware that Active Record (models in Rails) come with validations feature with it. So you do not need to create an index for that propose, and also not making a compose id.

+info => http://guides.rubyonrails.org/active_record_validations_callbacks.html#uniqueness

You might want to see also what a db index is: http://en.wikipedia.org/wiki/Database_index

After reading that, you should understand that the the only reason to create that index if you need to speed up querys like:

SELECT * FROM listings WHERE telephone=123123 AND name="John" AND latitude=123 AND longitude=323

And I'm pretty sure you don't need to make such a query where you already know all those values.

Upvotes: 2

Related Questions