Does uniqueness: true on my model duplicate unique: true in a db index

Question

I'm trying to debug an error I'm getting from Sentry:

PG::UniqueViolation: ERROR:  duplicate key value violates unique constraint "index_authors_on_fb_username"
DETAIL:  Key (fb_username)=(fakeUser) already exists.

I recently added uniqueness: true to the following validation in my model file validates :medium_username, presence: true.

I just realized that before that, I already had add_index :authors, :fb_username, unique: true from an old validation that I'd created.

FWIW, I create the author this way

author = Author.find_or_create_by(fb_id: json["userId"])

then set some attributes of the author and then get the error occasionally when I call author.save. I can't seem to recreate locally.

Is the model validation necessary? Is it a duplicate? Is the db validation enough?

Thanks!

Answer 1

Is the model validation necessary? Is it a duplicate? Is the db validation enough?

Yes. No. No.

You need both if uniqueness is actually important.

Model validations simply catch most uniqueness violations before they happen and provide the user with feedback. They do not actually guarantee that your database table will not contain rows with duplicate values since they are prone to race conditions.

This isn't even actually a rare condition. Any double-clicking senior citizen can potentially create a race condition unless you have a throttle in place.

A unique index guarantees on the database level that two duplicate values cannot be inserted into the table as the DB will reject any insert (or update) that violates the constraint. This is vital if uniqueness is actually important. But when you try to insert a value and it violates a unique constrain a database driver error is raised which will potentially crash your application, which is not want you typically would want.