Can Rails deal with DB uniqueness without index?

Question

I see add_index ~ unique: true statement in schema.rband think uniqueness is constraint for table, not for index.Using index is one of way realizing uniqueness, Programmer should not designate to the RDBMS "how" and index quicken searching but take costs inserting. In fact, is there another way to keep uniqueness constraint? If not, why Rails provide us only add_index statement for uniqueness constraint?

Answer 1

Because there is no need for other ways. Under the hood it's all the same: when you define a UNIQUE constraint, a UNIQUE index is created on that table to enforce it.

• Question from DBA.SE: When should I use a unique constraint instead of a unique index?

  So for a database that supports both features the choice of which to use will often come down to preferred style and consistency.

  So using indexes everywhere in Rails is consistent. That's the reason.

• A cite from PostgreSQL's docs:

  PostgreSQL automatically creates a unique index when a unique constraint or primary key is defined for a table. The index covers the columns that make up the primary key or unique constraint (a multicolumn index, if appropriate), and is the mechanism that enforces the constraint.

I haven't found the related cite for MySQL in an authoritative source, but "rumors are everywhere".

Answer 2

The only safely way to guarantee the uniqueness constraint is to enforce it at database level. There is nothing wrong with it, in fact, it's the real correct way. Any database designer or administrator will confirm it to you.

Rails provides an uniqueness validator, but it's not completely safe. In fact, it's based on a simple query against your database, but there is a small time lapse between the select is performed and the insert is executed that makes it possible (and very probably in high concurrent systems or if your insert takes a reasonable amount of time) to have a duplicate record created because of this race condition.

Therefore, the only way to guarantee uniqueness is to have an unique index on the database. If you are concerned about it, you may want to read some database design book or resource to re-evaluate the problem under a different point of view.

The reason why you still want to have a validation in your model, is because the database (generally) doesn't put any effort into trying to solve the conflict in a user friendly way. The validation in your model will pre-validate the data and prevent an exception to be raised by the DBMS. You can check the validation result and display a friendly message to the user.

Still, the database constraint will be there and in case your validation passes because of the race condition mentioned before, your data integrity will be ensured by the DBMS that will raise an exception.