how to make a unique column in rails active records other than id

Question

class CreateUsers < ActiveRecord::Migration
   def up
     create_table :users do |t|
        t.string "email", :unique=>true

I was trying to add this unique option for making this email id unique in rails model but its not working so how can i make this email id unique ?

Solution 1

The unique option should be passed to an add_index call, not where you define your column.

Update your migration definition as follows:

class CreateUsers < ActiveRecord::Migration
   def up
     create_table :users do |t|
        t.string "email"
     end
     add_index :users, :email, unique: true
   end
   ...
end

And if you don't want to allow null then use t.string :email, null: false.

Solution 2

Update:
Right now I would advise against ignoring the index on the database level, you could check the answer below to find out how to add a database unique index.

The reason this is not a good idea is because of the possibility of a race condition, when more than one thread/process of the application would get a request to insert the same data in almost the same time, both threads would check if the email already exists in the database or not, and both would find out that the email does not exist at that moment, so both will pass the #validate check, then both would proceed to insert.
Then you would end up with having the same email in the database with no idea how that happened.

Having the validation is useful because you can react to the invalid record error in your controller.

Original Answer:
Why put restrictions on db level, I think putting a validation in the model level is more than enough, and at least whenever you need to change or remove restrictions you don't find your self needing to add a new migration

class User < ActiveRecord::Base
  validates :email, uniqueness: true
end

Comments

[Sander Garretsen]

what makes you think it is 'not working'?

[Paulo Fidalgo]

Because of data integrity!

[Mohammad AbuShady]

@PauloFidalgo I understand, but when you have a table with millions of records and you want to update your index, you might end up locking your table for hours, that's why I think using code level checks is more practical.

[jefflunt]

Also, not every database is limited to being accessed via Rails, so the constraints are safest to put in the database level, so that any application that may write to the database will have the same set of rules to follow. The use of the validations in Rails are primarily to (a) get helpful error messages within the Rails app itself, and (b) to enable use of the valid? method, which can be quite useful, and possibly (c) to support other app-specific business logic.

[wizbcn]

Great! Post with full explanation: robots.thoughtbot.com/the-perils-of-uniqueness-validations

[Damon Drake]

@MohammadAbuShady so you're telling me that code checks will take the place of ACID in a RDBMS? Yes, code checks are valid for validity check, but there are just some items that shouldn't be ignored with consistency. I state this out of frustration with the many projects where this logic was applied and I had to go back through on custodian duty to clean it up and get the data back to a level that could be used by other systems. Complete neglect of system abilities just baffles me.

[Mohammad AbuShady]

Hi Damon, Now that I read this answer again, I don't think it's a good idea to just put the validation on the model without any indexes on the table it self. I've written this answer more than 5 year ago and I had certain experience with indexes that would make me want to avoid them. But at this moment I would advise against it, I will update my answer and thanks for bringing it to my attention