Using unique non clustered index with unique clustered index
Yes having a column in multiple unique keys is sometimes perfectly reasonable.  In the case that you gave above I'm not sure I would bother since the ProductId key is unique regardless.  But let's say that you have a product table like this:

ProductVendor  PK
ProductCode  PK
ProductDescription
.....


In this particular case the ProductVendor and ProductCode are together unique and are your primary key and clustered index.  However there is an additional business rule that ProductDescription must also be unique by ProductVendor.  In this case you could create a non-clustered index on ProductVendor, ProductDescription.