Re: all foreign key should have index?

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?

Microsoft SQL Server prevents you from creating a foreign key reference to a table and column if that column hasn't a constraint definied on it (unique or primary key), so mycol_id above must have a primary key or unique constraint definied upon it.

Tony.

-- 
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials


"Christopher Browne" <cbbrowne_at_acm.org> wrote in message 
news:m364o0fgnh.fsf_at_mobile.int.cbbrowne.com...


>>> No.  But better SQL products construct a "PK=FK"  index under the

>>> covers for you.

>>

>> Do you have a list of those?

>>

>> Is it configurable? I'm not sure I want my rdbms generating a 5GB index 

>> on

>> my foreign key when I might not require it.

>>

>> Backup bloat to start with.

>

> If the index required will be 5GB in size, then you probably can't

> operate _without_ it because each FK check would require a hideous Seq

> Scan across a 10GB table to do its check.

>

> If you want a case for *not* needing the index, use the one that's

> sensible, namely the case where the table referenced is only a few

> pages in size such that the sequential scan is expected to be faster

> than an index scan...

> -- 

> wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','gmail.com').

> http://linuxdatabases.info/info/lisp.html

> "640K ought to be enough for anybody" -- Bill Gates - 1981