Re: make a unique index for foreign keys?

Chris/ Josh-

OK-response to Chris below.  Synopsis here....simply by creating a foreign
key will not create an index.  On large tables I SHOULD put a non-unique
index on the foreign key (right?)


Hmm...nope.  Creating a foreign key doesn't create an index.  However,
creating a primary key does - sure you're not getting mixed up there?

You are absolutely right!  I am confused!!!! I did the actual test....

//create the tables

beth=> create table parent(pk INTEGER primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'parent_pkey'
for table 'parent'
CREATE
beth=> create table child (fk INTEGER references parent(pk) );
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
check(s) CREATE

// now get the index oid for the parent table (there should only be 1 index
for the primary key)

beth=> select pg_index.* from pg_index, pg_class where pg_class.oid =
pg_index. indrelid and pg_class.relname='parent';
indexrelid | indrelid | indproc | indkey | indclass | indisclustered |
indisunique | indisprimary | indreference |       ------------+----------+---------+--------+----------+--------------
--+-------------+--------------+--------------+---------
26271 |    26269 | -       | 1      |     1978 | f              | t
| t            |            0 |
(1 row)

// get the index for the parent table--and it is the index for the primary
key

beth=> select pg_get_indexdef(26271);
pg_get_indexdef                                           ------------------
------------------------------------------                     CREATE UNIQUE
INDEX parent_pkey ON parent USING btree (pk)                     (1 row)

//now try that same thing for the child table and there are NO indices (also
confirmed by \di)

beth=> select pg_index.* from pg_index, pg_class where pg_class.oid =
pg_index. indrelid and pg_class.relname='child';
indexrelid | indrelid | indproc | indkey | indclass | indisclustered |
indisunique | indisprimary | indreference |       ------------+----------+---------+--------+----------+--------------
--+-------------+--------------+--------------+---------
(0 rows)



Postgres doesn't create indices for you, unless as part of a primary key or
unique constraint.

OK-I've got it now!!!

Thank you so much!!!!
Beth