Right; relational databases should have at least one unique identifier for a row. However, there are multiple instances where you want to have more unique rows, in addition to the primary key.
One example would be a fleet management application with unique truck identifiers. You may want a UNIQUEID() primary key, but also a unique truck_id. You may want to keep them separate so that the truck_id can be renamed without breaking relationships that were tied to it. This thought holds true to any unique record where the possibility of renaming exists.
Also, if you use a valid_if statement like the above, you can give users immediate feedback, in the form of an error message, when they enter a duplicate id. Otherwise, AppSheets built-in duplicate id message only shows up when you attempt to save a record. And even then, the message can be confusing and it isn’t immediately obvious what went wrong.
Suffice to say, I find myself using the above pattern frequently.