Still learning… foreign keys don’t need to reference a primary key

…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

Leave a Reply

Your email address will not be published. Required fields are marked *