Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id AE09A2E0085 for ; Tue, 10 Jun 2008 02:18:57 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 50102-01-10 for ; Tue, 10 Jun 2008 02:18:42 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from wf-out-1314.google.com (wf-out-1314.google.com [209.85.200.168]) by developer.postgresql.org (Postfix) with ESMTP id 63ED52E0039 for ; Tue, 10 Jun 2008 02:16:56 -0300 (ADT) Received: by wf-out-1314.google.com with SMTP id 25so2309821wfc.28 for ; Mon, 09 Jun 2008 22:16:56 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:in-reply-to:mime-version:content-type:references; bh=zQ/BOxG4abvANO84PaF+GB7MNOdm1N2Ko+PjFfrpPVo=; b=s4TjmNlQkbtk9CHdFp0jRlpL1jX/v79YeJbt47TlLwIESj1MHiG8EeQu18mJPqQvx5 OiWP1xVUVaAZhSNCUtSp1mXDzrv0/ezwFESxc+J+ziTRsDGQ9xWasclHFxpPBu/usgTv RZEJR86aees2lmjDATRBR2MsWatJyMSF1491Y= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:in-reply-to:mime-version :content-type:references; b=mNErlQ8vte7do3nGTCjqpQdyFA/p/BQkHDmuLhyM0G3+eoisYbruBZJiNY/eDn8dG4 lA9TifDI3Y5mdqBnL76STP2PYzIO9Any+HhKYIH5WRvJAF3gJGxSOonUcvDuSCCQTT2J r1c7bg+HY/l/FTTk96L8EhE4j2L7TLCoZ79bw= Received: by 10.142.212.19 with SMTP id k19mr1851557wfg.13.1213075015361; Mon, 09 Jun 2008 22:16:55 -0700 (PDT) Received: by 10.142.211.2 with HTTP; Mon, 9 Jun 2008 22:16:55 -0700 (PDT) Message-ID: <2267dd9e0806092216n1dabf29ai6e7b82a727eaf5c5@mail.gmail.com> Date: Tue, 10 Jun 2008 13:16:55 +0800 From: "Tommy Cheng" To: pgsql-admin@postgresql.org Subject: Re: turn off auto index for foreign key In-Reply-To: <34d269d40806092158vc3069e7gfd58166dee46aa2c@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_1460_32987678.1213075015366" References: <2267dd9e0806091948k24a46cegcd45e65a5aa632ed@mail.gmail.com> <2267dd9e0806092117sda112dt8ac2f2ba8ed5e66f@mail.gmail.com> <34d269d40806092158vc3069e7gfd58166dee46aa2c@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200806/66 X-Sequence-Number: 29404 ------=_Part_1460_32987678.1213075015366 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit Content-Disposition: inline um...Your soln is possible. But if the user created an unique index using constraint name __key5, it will not be able to distinguish it from other auto index. On Tue, Jun 10, 2008 at 12:58 PM, Alex Hunsaker wrote: > On Mon, Jun 9, 2008 at 10:17 PM, Tommy Cheng wrote: > > you are right. I double checked. So, is there any way to turn off auto > index > > creation for the parent? (the best is changing postgresql database > setting) > > I am using postgres (PostgreSQL) 8.1.9 on CentOS 5 x86_64 > > > > Again, there is no auto index creation for foriegn keys. However if > you declared the column unique or its the pkey (ala unique) (As Scott > said) then there will an index. > > No you cant turn it off because its how postgres enforces uniqueness > see > http://www.postgresql.org/docs/8.1/interactive/index-unique-checks.html > for more > > My guess, use the name of the index to try to distinguish. Auto > generated ones will always be in the form: > pkey: _pkey(num) > unique: __key(num) > ------=_Part_1460_32987678.1213075015366 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit Content-Disposition: inline um...Your soln is possible. But if the user created an unique index using constraint name <table_name>_<column_name>_key5, it will not be able to distinguish it from other auto index.

On Tue, Jun 10, 2008 at 12:58 PM, Alex Hunsaker <badalex@gmail.com> wrote:
On Mon, Jun 9, 2008 at 10:17 PM, Tommy Cheng <csckcac@gmail.com> wrote:
> you are right. I double checked. So, is there any way to turn off auto index
> creation for the parent? (the best is changing postgresql database setting)
> I am using postgres (PostgreSQL) 8.1.9 on CentOS 5 x86_64
>

Again, there is no auto index creation for foriegn keys.  However if
you declared the column unique or its the pkey (ala unique) (As Scott
said) then there will  an index.

No you cant turn it off because its how postgres enforces uniqueness
see http://www.postgresql.org/docs/8.1/interactive/index-unique-checks.html
for more

My guess, use the name of the index to try to distinguish.  Auto
generated ones will always be in the form:
pkey: <table_name>_pkey(num)
unique: <table_name>_<column_name>_key(num)

------=_Part_1460_32987678.1213075015366--