问题描述
首先，任何人都可以解释唯一索引如何在数据库中工作吗？
Firstly, can anyone explain how unique index works in databases?
假设我有一个带有 name列的User模型并在其上添加唯一索引，但在模型（user.rb）中，我只是具有存在在名称字段上的验证器。
Suppose I have a User model with a name column and I add a unique index on it but in the model (user.rb) I just have a presence validator on the name field.
现在，当我尝试创建两个具有相同名称的用户时，我收到PGError
So now when I'm trying to create two users with same name, I get PGError
所以在我看来，唯一索引与唯一性验证器（？）相同
So It looks to me like the unique index working same as uniqueness validator(?)
如果是，那么外键又如何呢？
If so then what about foreign keys?
让我说我有 Post 模型，其中属于：user 与 User has_many：posts 关联。
和 posts表中具有唯一索引的外键 user_id 。然后，多个帖子不能具有相同的 user_id 。
Lets say I have Post model with belongs_to :user association with User has_many :posts.And a foreign key user_id in the posts table with unique index. Then multiple posts cannot have a same user_id.
有人可以解释唯一索引的工作方式吗？
Can someone explain how unique index works?
我正在使用Ruby 2.0.0的Rails 4。
I'm on Rails 4 with Ruby 2.0.0.
推荐答案
以下是唯一索引和validates_uniqueness_of的区别
Here are the difference between unique index and validates_uniqueness_of
此是一个修补程序，使ActiveRecord能够针对唯一约束违例识别数据库生成的错误。例如，它在不声明validates_uniqueness_of的情况下进行了以下工作：
This is a patch to enable ActiveRecord to identify db-generated errors for unique constraint violations. For example, it makes the following work without declaring a validates_uniqueness_of:
create_table "users" do |t|
t.string "email", :null => false
end
add_index "users", ["email"], :unique => true
class User < ActiveRecord::Base
end
User.create!(:email => '[email protected]')
u = User.create(:email => '[email protected]')
u.errors[:email]
=> "has already been taken"
好处是速度，易用性和完整性-
The benefits are speed, ease of use, and completeness --
速度
使用这种方法，您无需进行数据库查找即可检查保存时的唯一性（如果缺少索引，有时可能会很慢- ...）。如果您真的很在意验证唯一性，则无论如何都将不得不使用数据库约束，以便数据库无论如何都将验证唯一性，并且这种方法可以消除额外的查询。对于数据库来说，两次检查索引不是问题（它已被第二次缓存了），但是从应用程序中保存数据库往返是一个很大的胜利。
With this approach you don't need to do a db lookup to check for uniqueness when saving (which can sometimes be quite slow when the index is missed -- https://rails.lighthouseapp.com/projects/8994/tickets/2503-validate... ). If you really care about validating uniqueness you're going to have to use database constraints anyway so the database will validate uniqueness no matter what and this approach removes an extra query. Checking the index twice isn't a problem for the DB (it's cached the 2nd time around), but saving a DB round-trip from the application is a big win.
易于使用
鉴于您必须具有数据库约束才能获得真正的唯一性，因此这种方法将使所有一旦数据库就自动发生的事情约束到位。
Given that you have to have db constraints for true uniqueness anyway, this approach will let everything just happen automatically once the db constraints are in place. You can still use validates_uniqueness_of if you want to.
Completeness
validates_uniqueness_of有，您仍然可以使用validates_uniqueness_of。总是有点骇人听闻-它无法正确处理竞争条件，并导致必须使用某种程度的冗余错误处理逻辑来处理异常。 （请参见。）
validates_uniqueness_of has always been a bit of a hack -- it can't handle race conditions properly and results in exceptions that must be handled using somewhat redundant error handling logic. (See "Concurrency and integrity" section in http://api.rubyonrails.org/classes/ActiveRecord/Validations/ClassMe...)
validates_uniqueness_of 不足以确保值的唯一性。原因是在生产中，多个工作进程可能会导致竞争条件：
validates_uniqueness_of is not sufficient to ensure the uniqueness of a value. The reason for this is that in production, multiple worker processes can cause race conditions:
-
两个并发请求尝试创建一个用户具有相同的名称（和
，我们希望用户名是唯一的）
Two concurrent requests try to create a user with the same name (andwe want user names to be unique)
请求由服务器上的两个工作进程接受，这些工作进程的
现在将并行处理它们
The requests are accepted on the server by two worker processes whowill now process them in parallel
两个请求都扫描了用户表，并看到名称为
可用
Both requests scan the users table and see that the name isavailable
两个请求均通过验证，并使用看似
的可用名称创建用户
Both requests pass validation and create a user with the seeminglyavailable name
要获得更清晰的了解，请选中此
如果为列创建唯一索引，则意味着可以保证表格最多只能包含一行该列具有相同值的行。在模型中仅使用validates_uniqueness_of验证不足以实施唯一性，因为可能会有并发用户试图创建相同的数据。
If you create a unique index for a column it means you’re guaranteed the table won’t have more than one row with the same value for that column. Using only validates_uniqueness_of validation in your model isn’t enough to enforce uniqueness because there can be concurrent users trying to create the same data.
想象一下，两个用户试图注册一个具有与您在用户模型中添加validates_uniqueness_of：email的电子邮件相同的帐户。如果他们同时按下注册按钮，Rails会在用户表中查找该电子邮件，并回复一切正常，并且可以将记录保存到表中。然后，Rails将使用相同的电子邮件将这两个记录保存到用户表中，现在您遇到了一个很糟糕的问题。
Imagine that two users tries to register an account with the same email where you have added validates_uniqueness_of :email in your user model. If they hit the "Sign up" button at the same time, Rails will look in the user table for that email and respond back that everything is fine and that it’s ok to save the record to the table. Rails will then save the two records to the user table with the same email and now you have a really shitty problem to deal with.
为避免这种情况，您需要创建一个
To avoid this you need to create a unique constraint at the database level as well:
class CreateUsers < ActiveRecord::Migration
def change
create_table :users do |t|
t.string :email
...
end
add_index :users, :email, unique: true
end
end
因此，通过创建index_users_on_email唯一索引，您将获得两个非常不错的好处。数据完整性和良好的性能，因为唯一索引通常会非常快。
So by creating the index_users_on_email unique index you get two very nice benefits. Data integrity and good performance because unique indexes tends to be very fast.
如果在user_id的帖子表中输入unique：true，则不允许输入重复记录并使用相同的user_id。
If you put unique: true in your posts table for user_id then it will not allow to enter duplicate records with same user_id.
这篇关于跟踪唯一索引的区别和validates_uniqueness_of的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！