Rails：如何设置唯一的可互换索引约束

Setting uniqueness validation in rails is something you’ll end up doing quite often. Perhaps, you even already added them to most of your apps. However, this validation only gives a good user interface and experience. It informs the user of the errors preventing the data from being persisted in the database.

在rails中设置唯一性验证是您经常要做的事情。 也许，您甚至已经将它们添加到了大多数应用程序中。 但是，此验证只能提供良好的用户界面和体验。 它向用户通知错误，阻止数据保留在数据库中。

为什么唯一性验证还不够 (Why uniqueness validation is not enough)

Even with the uniqueness validation, unwanted data sometimes gets saved in the database. For clarity, let’s take a look at a user model shown below:

即使进行了唯一性验证，有时也会将不需要的数据保存在数据库中。 为了清楚起见，让我们看一下下面显示的用户模型：

class User
    validates :username, presence: true, uniqueness: true
end

To validate the username column, rails queries the database using SELECT to see if the username already exists. If it does, it prints “Username already exists”. If it doesn’t, it runs an INSERT query to persist the new username in the database.

为了验证用户名列，Rails使用SELECT查询数据库以查看用户名是否已经存在。 如果是这样，它将显示“用户名已存在”。 如果没有，它将运行INSERT查询以将新的用户名保留在数据库中。

When two users are running the same process at the same time, the database can sometimes save the data regardless of the validation constraint and that is where the database constraints (unique index) comes in.

当两个用户同时运行同一进程时，无论验证约束如何，数据库有时都可以保存数据，这就是数据库约束(唯一索引)所在的地方。

If user A and user B are both trying to persist the same username into the database at the same time, rails runs the SELECT query, if the username already exists, it informs both users. However, if the username doesn’t exist in the database, it runs the INSERT query for both users simultaneously as shown in the image below.

如果用户A和用户B都试图同时将同一用户名持久化到数据库中，则Rails将运行SELECT查询，如果用户名已经存在，它将通知两个用户。 但是，如果用户名在数据库中不存在，它将同时为两个用户运行INSERT查询，如下图所示。

Now that you know why the database unique index (database constraint) is important, let’s get into how to set it. It’s quite easy to set database unique index(es) for any column or set of columns in rails. However, some database constraint in rails can be tricky.

现在您已经知道为什么数据库唯一索引(数据库约束)很重要，让我们开始了解如何设置它。 为rails中的任何列或一组列设置数据库唯一索引非常容易。 但是，rails中的某些数据库约束可能很棘手。

快速查看为一个或多个列设置唯一索引 (A quick look at setting a unique index for one or more column)

This is quite as simple as running a migration. Let’s assume we have a users table with column username and we want to ensure that each user has a unique username. You simply create a migration and input the following code:

这与运行迁移非常简单。 假设我们有一个带有用户名列的用户表，并且我们要确保每个用户都有一个唯一的用户名。 您只需创建一个迁移并输入以下代码：

add_index :users, :username, unique: true

Then you run the migration and that’s it. The database now ensures that no similar usernames are saved in the table.

然后运行迁移，仅此而已。 现在，数据库确保没有类似的用户名保存在表中。

For multiple associated columns, let’s assume we have a requests table with columns sender_id and receiver_id. Similarly, you simply create a migration and input the following code:

对于多个关联的列，假设我们有一个带有表sender_id和receiver_id的请求表。 同样，您只需创建一个迁移并输入以下代码：

add_index :requests, [:sender_id, :receiver_id], unique: true

And that’s it? Uh oh, not so fast.

就是这样吗？ 嗯，不是那么快。

上面的多列迁移问题 (The problem with the multiple column migration above)

The problem is that the ids, in this case, are interchangeable. This means that if you have a sender_id of 1 and receiver_id of 2, the request table can still save a sender_id of 2 and receiver_id of 1, even though they already have a pending request.

问题在于，在这种情况下，这些ID是可互换的。 这意味着，如果sender_id为1，receiver_id为2，则请求表仍可以保存sender_id为2，receiver_id为1，即使它们已经有未决请求。

This problem often happens in a self-referential association. This means both the sender and receiver are users and sender_id or receiver_id is referenced from the user_id. A user with user_id(sender_id) of 1 sends a request to a user with user_id(receiver_id) of 2.

此问题通常发生在自指关联中。 这意味着发送方和接收方都是用户，并且从user_id引用sender_id或receive_id。 user_id(sender_id)为1的用户向user_id(receiver_id)为2的用户发送请求。

If the receiver sends another request again, and we allow it to save in the database, then we have two similar requests from the same two users(sender and receiver || receiver and sender) in the request table.

如果接收方再次发送另一个请求，并且我们允许它保存在数据库中，则在请求表中，来自相同两个用户(发送方和接收方||接收方和发送方)的两个相似请求。

This is illustrated in the image below:

如下图所示：

常见修复 (The common fix)

This problem is often fixed with the pseudo-code below:

此问题通常通过以下伪代码解决：

def force_record_conflict
    # 1. Return if there is an already existing request from the sender to receiver 
    # 2. If not then swap the sender and receiver
end

The problem with this solution is that the receiver_id and sender_id get swapped each time before saving to the database. Hence, the receiver_id column will have to save the sender_id and vice versa.

该解决方案的问题在于，每次将receive_id和sender_id交换一次，然后再保存到数据库中。 因此，receiver_id列将必须保存sender_id，反之亦然。

For example, if a user with sender_id of 1 sends a request to a user with receiver_id of 2, the request table will be as shown below:

例如，如果sender_id为1的用户向receive_id为2的用户发送请求，则请求表如下所示：

This may not sound like an issue but it’s better if your columns are saving the exact data you want them to save. This has numerous advantages. For example, if you need to send a notification to the receiver through the receiver_id, then you’ll query the database for the exact id from the receiver_id column. This already became more confusing the moment you start switching the data saved in your request table.

这听起来似乎不是问题，但最好是您的列要保存要保存的确切数据。 这具有许多优点。 例如，如果您需要通过receiver_id向接收者发送通知，那么您将在数据库中查询receive_id列中的确切ID。 当您开始切换请求表中保存的数据时，这已经变得更加混乱。

正确的解决方法 (The proper fix)

This problem can be entirely resolved by talking to the database directly. In this case, I’ll explain using PostgreSQL. When running the migration, you must ensure that the unique constraint checks for both (1,2) and (2,1) in the request table before saving.

通过直接与数据库对话可以完全解决此问题。 在这种情况下，我将说明如何使用PostgreSQL。 运行迁移时，必须确保在保存之前，唯一约束对请求表中的(1,2)和(2,1)进行检查。

You can do that by running a migration with the code below:

您可以通过使用以下代码运行迁移来做到这一点：

class AddInterchangableUniqueIndexToRequests < ActiveRecord::Migration[5.2]
    def change
        reversible do |dir|
            dir.up do
                connection.execute(%q(
                    create unique index index_requests_on_interchangable_sender_id_and_receiver_id on requests(greatest(sender_id,receiver_id), least(sender_id,receiver_id));
                    create unique index index_requests_on_interchangable_receiver_id_and_sender_id on requests(least(sender_id,receiver_id), greatest(sender_id,receiver_id));
                ))
            end

            dir.down do
                connection.execute(%q(
                    drop index index_requests_on_interchangable_sender_id_and_receiver_id;
                    drop index index_requests_on_interchangable_receiver_id_and_sender_id;
                ))
            end    
        end
    end
end

代码说明 (Code explanation)

After creating the migration file, the reversible is to ensure that we can revert our database whenever we must. The dir.up is the code to run when we migrate our database and dir.down will run when we migrate down or revert our database.

创建迁移文件后，可逆操作是确保我们可以在需要时还原数据库。 dir.up是在迁移数据库时运行的代码，而dir.down将在我们向下迁移或还原数据库时运行。

connection.execute(%q(...)) is to tell rails that our code is PostgreSQL. This helps rails to run our code as PostgreSQL.

connection.execute(%q(...))告诉Rails我们的代码是PostgreSQL。 这有助于Rails将我们的代码作为PostgreSQL运行。

Since our “ids” are integers, before saving into the database, we check if the greatest and least (2 and 1) are already in the database using the code below:

由于我们的“ id”是整数，因此在保存到数据库之前，我们使用以下代码检查最大和最小(2和1)是否已存在于数据库中：

requests(greatest(sender_id,receiver_id), least(sender_id,receiver_id))

Then we also check if the least and greatest (1 and 2) are in the database using:

然后，我们还使用以下方法检查数据库中最小和最大(1和2)是否存在：

requests(least(sender_id,receiver_id), greatest(sender_id,receiver_id))

The request table will then be exactly how we intend as shown in the image below:

然后，请求表将完全符合我们的预期，如下图所示：

And that’s it. Happy coding!

就是这样。 编码愉快！

参考文献： (References:)

Edgeguides | Thoughtbot

边缘导向 | 思想机器人

翻译自: https://www.freecodecamp.org/news/how-to-set-unique-interchangeable-index-constraint-in-rails/