I personally loved the topic on partial indexes and how it solves the use case of uniqueness in case there are soft deleted rows. (Since every NULL is considered a unique value)
It is worth mentioning however, that partial indexes are not yet supported in every database engine including the one in the latest version of MySQL.