Главный смысл существования альтернативного ключа заключается в том, чтобы сформировать дополнительное ограничение на уникальность строк в таблице. Обычно, наличие такого ключа говорит о понижении степени нормализации БД, так как в таблице появляются как бы 2 основных ключа, то есть данные зависят от 2х разных ключевых комбинаций полей.
Необходимость его введения чаще всего вызывается появлением в таблице суррогатного ключа в качестве основного, например, во всякого рода справочниках типа (ID, Name), так как, кроме уникальности по ID, требуется уникальность по Name. Основной минус в добавлении альтернативного ключа заключается в замедлении скорости операций модификации(вставка, изменение и удаление) данных, хотя и не всегда сильно, если только он не кластерный. Можно проверять уникальность и самому, но уже в коде, но проще, разумеется, альтернативным ключом.
Зато можно использовать альтернативный ключ в качестве обычного индекса, если в самом начале ключа поставить те поля, которые используются в других, более нужных, индексах с исключением последних, так как альтернативному ключу абсолютно безразлично в каком порядке в нем присутствуют поля, лишь бы их комбинация гарантировала уникальность.