Monday 7 January 2019

Understanding Unique Constraints

I'm looking at a database from a vendor, who shall be Nameless.

It contains a table called tblConfig.  OK, I know some people hate to see tables with the prefix tbl, but I can see the point to it.  That isn't my issue

I'm reviewing indexes, with a view to getting rid of duplicates.  There really is no point in having two indexes doing the same thing - SQL Server will only use one of them, but it has to keep both updated.

And that's where I found it:

There is an index on that table called UniqueConstraint.  

There is another index, also called UniqueConstraint.  

One is unique, the other isn't, which is how the developer got away with it.  

The index UniqueConstraint which is unique indexes different fields to its namesake index UniqueConstraint  which is not unique.  

There is also an index called UniqueConstraintReporting.  This duplicates the index UniqueConstraint which is not unique.  UniqueConstraintReporting is unique.

I've dropped the UniqueConstraint index which is not unique, leaving me with the unique indexes UniqueConstraint and UniqueConstraintReporting

I hope that is all clear to everyone?

No comments:

Post a Comment