Using unique non clustered index with unique clustered index

Question

Would there be any scenario where it would make sense to create a unique non clustered index that includes a column that is contained in a unique clustered index?

In other words, if I have a Products table with a unique clustered index on the ProductID column. Would there be an acceptable scenario where I would need to create a unique non clustered index that includes the ProductID column in addition to a couple of other columns, or, would the mere inclusion of the unique ProductID column make creating the non clustered index unique irrelevant?

• Martin Smith: If you have overlapping candidate keys the table is not in BCNF.
• mustaccio: @MartinSmith: index != key
• Martin Smith: @mustaccio - Two unique indexes means two candidate keys.
• mustaccio: That is true of course but generally indexes (including unique ones) exist also for other reasons, don't they.
• Martin Smith: @mustaccio - True. I can think of a couple of situations where you might want this but there is no overlapping candidate key actually. A much narrower NCI on the same column as the CI might be justified in some cases. Also supertype/subtype pattern needs a redundant unique constraint for the FK.

Answer 1

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.

• Nico: Aha, we are narrowing down the answer. So the question is would there be any reason why one would want to make the non-clustered index on ProductVendor, ProductDescription unique?
• Kenneth Fisher: It could simply be a business rule that vendor cannot name more than one product the same thing. They also cannot have the same code for two different products. Because you are storing multiple vendors the ProductVendor column will have to be in both unique indexes.
• Nico: Thanks for your input. This has helped me to clarify in my mind that the business rule may require a unique combination of columns even though one or more of the columns in itself may be unique. Thanks for your help.