Re: all foreign key should have index?

In message <dro97b$gn9$1$830fa7a5_at_news.demon.co.uk>, Tony Rogerson <tonyrogerson_at_sqlserverfaq.com> writes
>That would be a look up on the primary key though, rather, the key you've
>reference on the foreign key clause....
>
>So in this example...
>
>create table test2 (
> mycol_id int not null constraint pk_test2 primary key clustered
> )
>
>create table myothertable (
> blah int not null constraint pk_myothertable primary key clustered,
> mycol_id int not null references test2 ( mycol_id )
> )
>
>What is the point of indexing mycol_id?

When you delete a parent row, or update its key, the DBMS needs to find the children, either to cascade the operation or to forbid it - how can it do this efficiently without an index? But of course if you never do those things,
you might still need it to find child rows efficiently anyway.

-- 
Eric Junkermann