We have a large table
[MyTable] which currently has both a
Primary Key, and a
Unique Non Clustered Index on the same column (
[KeyColumn]). The U NC index also has additional covering columns.
Having both PK and Unique NC Index on the same columns seems redundant, so I was considering deleting the Primary Key and instead using the Unique Non Clustered index for Referential Integrity purposes.
Note that the table is clustered by another column entirely.
i.e. So we have:
ALTER TABLE [MyTable]
ADD CONSTRAINT [PK_MyTable]
PRIMARY KEY NONCLUSTERED ([KeyColumn])
CREATE UNIQUE NONCLUSTERED INDEX [IX_MyTable_SomeIndex]
ON [MyTable] ([KeyColumn])
INCLUDE ([Column1], [Column2])
As far as I know, it is not possible to add covering columns to a Primary Key, so I intend to do:
- Drop the Foreign Key Constraints reliant on
- Drop the Primary Key on
- Re-Add the foreign keys to the table (i.e. RI will be enforced via
The only implication I can think of doing this is that we won’t get the visual key symbol on our ERD diagrams, and that the (leaf) index density will be less because of the included columns.
I've read https://stackoverflow.com/questions/487314/primary-key-or-unique-index and am happy with the integrity and performance aspects of doing this.
My Question is : Is this approach flawed?
Edit What I'm trying to accomplish : Performance Optimisation and Spring Cleaning. By removing either the PK or an Index, there will be less pages needed for my indexes = faster writes, plus also maintenance/operational benefits, i.e. one less index to keep defragged etc.
To put some background to this, I've never before had a table which is being referenced, without a PK. However, the fact that a NC Index with covering columns was added to the table means that I need to adapt my thinking.