Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

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

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
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 Feb 5 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 Feb 5 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 Feb 6 at 8:52
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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