We have a large table
[MyTable] which currently has both a PK, and a UNIQUE Non Clustered Index on
[KeyColumn] (which has additional covering columns), which seems redundant, and I think I can delete the Primary Key and instead use 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]) GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_MyTable_SomeIndex] ON [MyTable] ([KeyColumn]) INCLUDE ([Column1], [Column2]) GO
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 only implication I can think of doing this is that we won’t get a key symbol on our ERD diagrams, and that the index density will be less because of the included columns.
I've read http://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.