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

August 5, 2009

…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);
create unique index ixOther on testunique(otherid);
create table testFK (id int identity(1,1) primary key, someid int)
alter table testFK add constraint fkTest foreign key (someid) references testunique(otherid)

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

LobsterPot Blogs

Blog posts by Rob Farley and other LobsterPot Solutions team members.


Related Blogs