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.