Nadrek (10/10/2011)

---

"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 would like to point out that if you didn't use WITH CHECK CHECK to fully rescan those tables and revalidate the constraint, those FK's are untrusted by SQL Server, and do not provide their full benefits.

okbangas (10/11/2011)

---

This does indeed happen on both SQL Server 2008 R2 (RTM) and SQL Server 2008 SP2:

use tempdb;
go

create table t1 (
  id int identity primary key,
  val varchar(10)
);

create table t2 (
  id int identity primary key,
  val varchar(10)
);
go

create unique nonclustered index ix_t1 on t1 (id);
go

alter table t2
  add constraint fk_t2_t1 foreign key (id) references t1 (id);
go

select 
	f.name as ConstraintName, 
	i.name as IndexName, 
	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    
		inner join 
	sys.indexes i on 
			i.object_id = f.referenced_object_id and 
			i.index_id = f.key_index_id
go
			
drop table t2;
drop table t1;

Actually, Kimberly Tripp has even a Post on it. As far as I can see, this is a very nice feature optimizing the performance of foreign key constraint validation, and not even close to a bug.

Msg 3723, Level 16, State 6, Line 2
An explicit DROP INDEX is not allowed on index 't1.ix_t1'. It is being used for FOREIGN KEY constraint enforcement.

rstelma (10/11/2011)

---

Hi,

I hope you guys can help me get something straightened out here. I ran this query on the Master DB:

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

With these results.

name name tablename is_unique is_primary_key type_desc key_index_id
FK_ProjectWorkOrders_Projects PK_Projects Projects 1 1 CLUSTERED 1
FK_ProjectFacilities_Projects PK_Projects Projects 1 1 CLUSTERED 1
FK_WorkOrderTypeEmployees_WorkOrderTypes PK_WorkOrderTypes WorkOrderTypes 1 1 CLUSTERED 1
FK_ProjectWorkOrders_WorkOrderTypes PK_WorkOrderTypes WorkOrderTypes 1 1 CLUSTERED 1
FK_ProjectWorkOrderEmployees_ProjectWorkOrders PK_ProjectWorkOrders ProjectWorkOrders 1 1 CLUSTERED 1

I also ran this query on the DB where these tables are and got 156 results which is pretty much what I expected.

Why are only some of the indexes showing up in the Master DB? These are the most recent table additions and foreign keys.

Very sorry if this is too far off topic. If so I will repost as a new thread.

Thank you,
Richard