…but you should still have a primary key on every table of course.
It’s just that I only recently discovered that you can have a foreign key that references something else, so long as it’s known to be unique through a unique index / constraint.
The scripts here demonstrate this in SQL Server 2005 and beyond.
create table testunique (id int identity(1,1) primary key, otherid int);
go
create unique index ixOther on testunique(otherid);
go
create table testFK (id int identity(1,1) primary key, someid int)
go
alter table testFK add constraint fkTest foreign key (someid) references testunique(otherid)
go
And then if I try to drop the ixOther index, I get an error saying:
Msg 3723, Level 16, State 6, Line 1
An explicit DROP INDEX is not allowed on index ‘testunique.ixOther’. It is being used for FOREIGN KEY constraint enforcement.
So I guess this is another reason not to blindly remove indexes that aren’t mentioned in sys.dm_db_index_usage_stats
