Why validate uniqueness in Rails model if there is a unique index on the database?



I know that it isn’t sufficient to only add uniqueness validation at the model level, that you need to also add a unique index to the database table as well, due to these “perils”. My question is why don’t Rails developers just use the unique index by itself? You will need to handle RecordNotUnique exceptions anyway, so why not make that the single source of truth? It seems like the validation is adding some overhead because it requires a select before the insert, rather than just attempting the insert and handling the result.


Each of these things actually provides some value that the other does not. You’re right that index provides ultimate data integrity protection. But the validation on the model provides app level validation and enables niceties like form validation errors. The unique index will not do that out of the box. You could possibly intercept the RecordNotUnique exception and wire up the form validation helpers, but the model validation will do this for you in the majority of cases.


Just to expand a little on @dustyburwell’s answer, I think there’s a problem with only using a unique index, in that Rails would depend on the way the underlying database library reports the error. The validation error raised should indicate the column that caused the problem, so this information can be passed to the user, and this would be problematic using only a DB constraint.

With a uniqueness validation in ActiveRecord, this is simple to do: the call to save the model does a SELECT to determine whether there would be a duplicate value for any column with a uniqueness validation on it. If the SELECT query comes back positive for a column, an error can be added related to that specific attribute. I wouldn’t worry about the performance impact of doing the existence check - the query shouldn’t cause loading or transfer of any data, and if you have any sort of index on the column, it will just perform an index scan until it finds a match (very fast).

With the unique index on the database, the error message reported back by the underlying database engine may vary. Checking in an application using PostgreSQL, as an example, you get a PG::UniqueViolation exception, and in my specific test case I got the following exception reported:

ActiveRecord::RecordNotUnique: PG::UniqueViolation: ERROR:  duplicate key value violates unique constraint "index_users_on_email"
DETAIL:  Key (email)=(user@example.com) already exists.

I had a quick look at the libpq C library that the pg gem links to, and it looks like there is an error code specific to uniqueness violations, but any further details of the error are provided in a string format as displayed in the example above. Now, in order to implement the same level of error reporting as the model validations, Rails would need to parse this string error message to determine the offending column. The exact formatting of this error message may actually change dependent on the version of the underlying client C library installed, or the version of the server being connected to, and this whole approach starts to appear a little unreliable.

Furthermore, this is just one database storage engine that Rails supports - others may not report any detail of which column/value caused a uniqueness violation. So in order to make the approach generic, Rails chooses to implement the validations at a higher level, and give you the option to add database-level integrity constraints.

So, I think the short answer as to why Rails developers don’t just use unique indexes, is that it doesn’t provide for a lot of the features that Rails gives you with the model validations.