Re: FKs and indexes

["Shulzhenko Vasyl" <vasilis@softline.kiev.ua>]

The short answer is no.  There is no difference (unless someone corrects me) in 
the behavior of an index that is automatically created vs. one that is manually 
created.

That said, there are a few reasons that you would want to manually create an 
index:
  1.  You can name the index (as you mentioned).  The immediate
      benefits of this are minimal EXCEPT THAT some ODBC tools 
      (like MS-Access) won't recognize indexes with a space for
      the first character.  If they don't recognize the primary
      key index, they can't determine that a given column (or
      set of columns) is a primary key, and can not determine
      relationships with other tables.  Also, any updates or
      deletes can create some really nasty WHERE clauses, with
      every column named and tested for equality, instead of only
      those columns in the primary key.
  2.  You can specify a non-default fillfactor.
  3.  You can specify a different dbspace, so that the index
      is detached from the table.  You can also make the index
      fragmented.  Either or both of these can improve 
      performance.

This is why the general consensus is that you should create the table, build the 
index, then alter the table to declare the primary/foreign keys.


> Here's another question for the group:  What are the differences between
> these two things:
> 
> -- First thing
> CREATE TABLE foo (
>     col1 INTEGER,
>     col2 INTEGER
>     );
> ALTER TABLE foo ADD CONSTRAINT PRIMARY KEY (col1) CONSTRAINT foo_pk;
> 
> -- Second thing
> CREATE TABLE foo (
>     {as above}
>     );
> CREATE UNIQUE INDEX foo_ix00 ON foo(col1);
> ALTER TABLE foo ADD CONSTRAINT PRIMARY KEY (col1) CONSTRAINT foo_pk;
> 
> 
> In the second way, you get to choose the name for the index, and in the
> first way, you don't.  But is there anything more subtle than that?  That
> is, does an implicitly created index behave any differently than an
> explicitly created one?



Mark Collins
mcollins@us.dhl.com