Re: all foreign key should have index?

My point is that I want the choice, not all situations require the index to be there and for those situations the overhead not only involves more storage but also incurrs more IO for logging, nodes in the B-Tree etc...

One such database would be in invest banking for instance, the trade table is insert only, modifications are stored as versions for history and audit purpose so you never get updates, you would get deletes though - for archiving, but thats a block of data and runs as a batch job rather than oltp.

So I suppose my statement would something along 'But better SQL products give you the option of constructing a "PK=FK" index for you'.

Tony.

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


"Eric Junkermann" <eric_at_deptj.demon.co.uk> wrote in message 
news:gep5EuDoD+3DFwKO_at_deptj.demon.co.uk...


> 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