Everywhere I see a post about ensuring uniqueness in a CQRS architecture the most obvious solution for me which is to add a unique index on the write side is never mentioned, without any explanation.
Instead I read the read model must be queried for that, and concurrency issues should be tackled by a saga compensating the action. Seems overly complex when you can just reject the command on unique index violation, so why is that?
Why does CQRS seem to prevent unique constraints on write side?
It doesn't
What it does do is recognize that that maintaining an invariant on a distributed set is a nightmare.
the most obvious solution for me which is to add a unique index on the write side is never mentioned, without any explanation
That's right. If you don't have a distributed set -- if all of the elements of the set are stored together -- then maintaining the invariant is straight forward.
But what does it mean to have a unique index constraint that spans two databases?
To express the idea in more modern terms, the guiding assumption is that the business logic should be scale agnostic. If two write models are really independent of one another, then we ought to be able to store them separately.
If there is a constraint that needs to be satisfied that depends on data from two different write models, then those write models aren't really independent.
Greg Young raised a really good question
What is the business impact of having a failure?
That's the sort of thing we are supposed to be thinking about in domain driven design, after all.
Why would event sourcing prevent me to put an index on unique fields??
Same answer really: it doesn't, so long as your unique constraint and your events are stored together.
If you have an RDMBS with a table that represents the elements of your set, and tables that store your events, you can update the two tables together within a single transaction, and roll back the whole mess if your constraint is violated.
But take that same idea, and put the set in a different database than the events? Now you have two distinct transactions to coordinate. Good luck with that.