Ring Ring. Ring Ring. "Don't answer that!", says the man sitting to the left of me, his black cape whisking in the wind behind him as we race down the dark city streets. The only escape from the darkness is the eerie bubble of light created by the streaming breath of fire emitting from the rear of our vehicle. Ring Ring.. Ring Ring.. I see the squawk of my droid is upsetting him. I think the thousands of birds I have hurled against poorly constructed buildings as of late have offended him and he smirks every time I detach my communicator from my utility belt. Ring Ring! Ring Ring! Things are becoming foggy. Ring Ring! Ring Ring! ...Is that a tunnel I'm being sucked through? Ring Ring!! Ring Ring!! ... "What's happeninggggg!"

Oh it's just another cool dream that I am being abruptly ripped from by the howling of my phone. A quick check of the clock shows its 2 AM. There must be a mild catastrophe brewing.

Wait, bats aren't related to birds are they? Was I Robin? Robins are birds? Have I been hurling myself against buildings that are doomed to fall? I'll have to solve this riddle later; there is somebody on the other end of this phone. Time for some real crusading!

The caller on the other end, a junior engineer, relays to me "we were upgrading our application at a client's site and they are getting a SQL error. The app is down. Can you help?" "Yes I can help, what is the error?" I say a few octaves above a whisper in a feeble attempt to avoid waking my wife. Too late! She's awake. I better get out quick. I roll out of bed, down the hall to the bat ca.....oops my mind is still half caught up in my dream...to the home office!

OK, enough of the dramatics. I am now wide awake dealing with a down system and the error is as follows:

Error: An explicit DROP INDEX is not allowed on index 'dbo.BaseTable.IX_BaseTable'. It is being used for FOREIGN KEY constraint enforcement.

The error is created when a part of a schema update script is run. We have run the script that contains this drop index at literally 100+ other sites without issue. What is different here? The time being late and my brain not willing to delve into the issue in depth (I think it wanted to get back to the dream, hopefully with me in the driver's seat next time) I went for the quick fix. I dropped all the foreign key constraints to this table on the handful of tables referencing it. I dropped the index, and then I added the foreign keys references back.

I had the engineer start the schema update again, and it worked! Back to sleep, I'll figure this out tomorrow. I hope Catwoman is in the next scene.

The next day I started to attempt to unravel this strangely wound web. I know, I know, wrong super hero reference. The engineer had dropped a backup of the database in my share on a file server, and I restored it to just before the schema update started. I began to look at the indexes and foreign keys. I noticed something that looked a little odd after running the below query, but it was not totally clear to me at the time.

Note: I believe I found this query on SQLServerCentral, but I am not certain who posted it. Thanks though!

select f.name
     , i.name
     , object_name(i.object_id) as tablename
     , i.is_unique,i.is_primary_key
     , i.type_desc
     , f.key_index_id 
 from sys.foreign_keys f 
   join sys.indexes i 
     on i.object_id = f.referenced_object_id 
     and i.index_id = f.key_index_id

The result set :

The first column is the name of the foreign key in the referencing table; the next column is the name of the index it references on the base table. My mind had a silent hmm moment when I saw this. The rest of the columns are self-explanatory except for possibly the last one. It references the index ID on the base table. So IX_BaseTable is index ID 1 in dbo.basetable. This is illustrated by this next query.

select object_id
     , name
     , index_id
     , type
     , type_desc
     , is_unuique
     , data_space_id
     , ignore_dup_key
     , is_primary_key
     , is_unique_constraint
 from sys.indexes
 where object_id = object_id('basetable') 
 or object_id = object_id('fktable')

The result set: (some columns omitted)

The hmm moment you might think was "Why does a foreign key constraint reference an index at all?" That's a debate for another day for those that like to discuss the differences between logical structures and physical structures. The hmm moment was "Why is it referencing the IX_BaseTable index and not the PK_BaseTable index?" I have never seen the above scenario before and I have seen a lot of foreign keys. In every other experience I have had the foreign key references the Primary Key index. Why is this one different? And is that why the error from last night occurred?

After some experimenting and research I was able to duplicate the problem and I determined a few things that you may or may not agree with. The answer to the first question is that the behavior is a defect in SQL 2005 SP4 (or all versions of 2005 possibly, I only tested SP4). The main reason I determined this is that it does not happen in 2008 SP2 or 2008R2 RTM. I can duplicate the problem without issue on 2005 SP4. In the 2008 versions tested the foreign key never references any other index than the PK_BaseTable index. Someone at Microsoft must have fixed this. I looked for a defect but I could not find one.

The answer to the second question is yes, this is why the error occurred last night. Since the foreign keys referenced the IX_BaseTable index the script that attempted to drop the index could not complete as the index was tied to the foreign keys. Dropping the foreign keys allowed me to then drop the index. And after applying the foreign keys back, wouldn't you know, the query results show that the foreign keys are referencing the Primary Key index.

So the only question now is why did the foreign keys reference the "wrong" index? I'll give you the series of events. When the table was created several years ago, somehow it was created with a primary key that was set to unique but not clustered. Later, after the lack of clustered index was discovered, someone added a delta script that applied IX_BaseTable which is a clustered index. The foreign keys from the referencing table had been in place for some time so adding the index did not affect the existing relationships. The event that created the scenario was that at some point, for a very specific reason that I will not get into, the foreign keys referencing this table were dropped on this database in a custom script for this client. Data was modified, and the foreign keys were then added back. Due to what I am calling a defect in SQL 2005 the newly recreated foreign keys were then associated with the IX_BaseTable index.

It looks to me that in 2005 when a foreign key is created that it picks the best available index to use. If the Primary Key is also a clustered index it will always pick that. If the primary Key is not clustered, and there is another index that is clustered and unique it will choose that one. Again in 2008 this does not happen. It always chooses the primary key under my test scenarios. I believe I have never seen this happen before as I have never created a primary key that was not also clustered that I can recall.

See the attached scripts to recreate the problem or in the case of 2008 and higher, to not recreate the problem.

I'll see you at the same bat time same bat... yeah, it's too corny. Have a great day.