SQL Server will not automatically create an index on a foreign key. Also from MSDN:
A FOREIGN KEY constraint does not have
to be linked only to a PRIMARY KEY
constraint in another table; it can
also be defined to reference the
columns of a UNIQUE constraint in
another table. A FOREIGN KEY
constraint can contain null values;
however, if any column of a composite
FOREIGN KEY constraint contains null
values, verification of all values
that make up the FOREIGN KEY
constraint is skipped. To make sure
that all values of a composite FOREIGN
KEY constraint are verified, specify
NOT NULL on all the participating
columns.