如何有条件地添加索引

本文介绍了如何有条件地添加索引的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

说，我有一个模型

class Post < ActiveRecord::Base validates_uniqueness_of :title, :unless => Proc.new {|p| p.deleted?} end

的约束是我可以有foobar的为题只有1个职位，而它的不被删除，并1+职位，它们都已经被删除是真实的，也有foobar的作为标题。由于ActiveRecord的不能此链接 ，我想添加一个唯一索引表中的职位，在列[：标题：删除]，它会失败的情况下，当我尝试插入一个新的删除后的分贝

The constraint is i can have only 1 post having "foobar" as its title while it's not deleted, and 1+ posts, which are all having deleted to be true, also having "foobar" as their titles. Since ActiveRecord can not guarantee the uniqueness of the title from this link, I'm trying to add a unique index to the table posts, on columns [:title, :deleted], it will fail the scenario when I try to insert a new deleted post to the db.

推荐答案

这是不可能有很多数据库比如有条件数据库索引MySQL的。

It is not possible to have a conditional database index with most databases e.g. MySQL.

也许是最好的选择，如果你绝对必须保证唯一性将有删帖的一个单独的表。你必须在主表中的唯一索引，但不能在含有被删除的记录表中​​。你会最终有一个较小的职位表，简单的编码 - 有不再需要任何疑问筛选出删帖

Probably the best option if you absolutely must guarantee uniqueness would be to have a separate table of deleted posts. You have the unique index on the main table, but not on the table containing deleted records. You would end up with a smaller posts table and simpler coding - there's no longer a need to filter out deleted posts in any queries.

你有没有考虑，如果你取消删除后会发生什么 - 可以两个职位则有相同的标题

Have you considered what should happen if you undelete a post - can two posts then have the same title?

一些其他的选项有：

更改标题，当你删除一个帖子，例如添加时间戳。

引发一个异常的模型保存后，如果你能找到的同名2个或多个实例。

保存（悲观锁）之前锁定整个表。

只是希望它的工作原理（多久你要两个人张贴在完全相同的时间相同的标题？）