0

IIRC, if I want to enforce uniqueness of a non-primary attribute, there are 2 ways

  1. Unique constraint
  2. Unique non-clustered index (assume the clustered index is occupied by another attribute already)

I find them somehow similar in enforcing uniqueness. However, when it come to modification, their behavior are quite different.

Suppose I would like to include an additional column to the unique nonclustered index. If I used an unique constraint, I can't just drop the index created by the constraint according to this post. So I need to drop the constraint and recreate the constraint. Effectively removing and creating a unique NCI.

Suppose I didn't apply the unique constraint during table creation. Instead, I opted for a unique NCI. It is from this post that I know can't alter index as though a table. I need to drop it before re-creating a new one. However, according to this post, I can utilize the

DROP_EXISTING=ON

to reduce the cost from removing and recreating an unique NCI.

Judging from all the information above, what is the justification of using unique constraint? Functionally it is the same as though declaring an unique NCI. Cost-wise it is way more expensive to modify it than simply modifying NCI.

Andes Lam
  • 192
  • 2
  • 8
  • 1
    A unique constraint is implemented as a unique index - so functionally they are the same as far as maintaining uniqueness is concerned. And AFAIK, there is no added cost as far as "altering" the index is concerned but there are syntax differences which creates a convenience factor. IMO the choice of a constraint vs. index expresses the designer's goal / intention more precisely. A diagramming tool will usually ignore a unique index but show a constraint. If you are altering constraints/indexes often, you have bigger problems to address. – SMor Jul 22 '21 at 11:29
  • And pay attention to the date / environment of questions you use as references. Functionality improves (or changes) with every version. Things have changed greatly since the 2005 version. – SMor Jul 22 '21 at 11:31

0 Answers0