Rajat Chauhan

Your database is the only thing that knows if a value is taken

Check-then-write breaks under row-level security: the clash you are looking for is filtered out before the query returns. Let the unique constraint answer instead.