3 ms·
That's what partial unique indexes allow you to put in the database. take this example which would work in PostgreSQL: CREATE UNIQUE INDEX profile_usernam
by einhverfr 13y ago
That's what partial unique indexes allow you to put in the database. take this example which would work in PostgreSQL:
CREATE UNIQUE INDEX profile_username_valid_idx_u
ON profile(username)
WHERE invalidated_at IS NULL;
The problem with enforcing this in app-space is it doesn't get around the isolation problem. In both cases, you are assuming that the same profile won't get concurrently updated twice by two different transactions. Since only the database can referee this issue, you must enforce the uniqueness there. I.e. this check must happen on transaction commit and your application has no way of knowing if it did.