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