3

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?

Improve this question
13
  • If you have overlapping candidate keys the table is not in BCNF.
    – Martin Smith
    Commented Jan 29, 2014 at 12:45
  • @MartinSmith: index != key
    – mustaccio
    Commented Jan 29, 2014 at 12:46
  • @mustaccio - Two unique indexes means two candidate keys.
    – Martin Smith
    Commented Jan 29, 2014 at 12:46
  • That is true of course but generally indexes (including unique ones) exist also for other reasons, don't they.
    – mustaccio
    Commented Jan 29, 2014 at 12:50
  • 1
    @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.
    – Martin Smith
    Commented Jan 29, 2014 at 12:53

1 Answer 1

Reset to default
2

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.

Improve this answer
3
  • 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?
    – Nico
    Commented Feb 5, 2014 at 14:11
  • 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.
    – Kenneth Fisher
    Commented Feb 5, 2014 at 15:48
  • 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.
    – Nico
    Commented Feb 6, 2014 at 8:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.