Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id ADAAB476ED7 for ; Thu, 13 Jun 2002 12:31:44 -0400 (EDT) Received: from lsh114.siteprotect.com (lsh114.siteprotect.com [66.113.130.247]) by postgresql.org (Postfix) with ESMTP id 43723476F82 for ; Thu, 13 Jun 2002 12:23:47 -0400 (EDT) Received: from bethvizx (ns2.vizxlabs.org [216.122.78.42]) by lsh114.siteprotect.com (8.9.3/8.9.3) with ESMTP id LAA22811; Thu, 13 Jun 2002 11:23:40 -0500 From: "Beth Gatewood" To: "'Christopher Kings-Lynne'" , Cc: Subject: Re: make a unique index for foreign keys? Date: Thu, 13 Jun 2002 09:23:35 -0700 Message-ID: <004701c212f6$ab821900$6501a8c0@bethvizx> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook CWS, Build 9.0.2416 (9.0.2911.0) In-Reply-To: Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4807.1700 X-Archive-Number: 200206/178 X-Sequence-Number: 8143 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