Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id 2371E2E0032 for ; Tue, 10 Jun 2008 01:58:29 -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 23607-03 for ; Tue, 10 Jun 2008 01:58:17 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from fk-out-0910.google.com (fk-out-0910.google.com [209.85.128.184]) by developer.postgresql.org (Postfix) with ESMTP id 42A772E0039 for ; Tue, 10 Jun 2008 01:58:16 -0300 (ADT) Received: by fk-out-0910.google.com with SMTP id e30so1381929fke.3 for ; Mon, 09 Jun 2008 21:58:15 -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:cc:in-reply-to:mime-version:content-type :content-transfer-encoding:content-disposition:references; bh=RhvxpBcN6gpfdbYvCv2wdOb8i6YkcQ1zml4e4/gi8rI=; b=QFUX7QNIM/lTTNxePmkFBmbI7YITdRkpvWT9MBzjzVy5Q0GMZtXt862PHpDqxriAz7 eVmF1McdOKjToyiPEOrxcQ71MxiY1d8WK6XTCHFaGVa6Tg0qPccZLSTKFsgSHgSJK0eK jTHPaaoZ+419I+nSlb6PAeeldryRiUYbtM8AY= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:content-transfer-encoding:content-disposition :references; b=wdgDhdC4l2l30qHQy/pZvzmS1S270Rk7k8c+DsXPxPnJ5U0civm5pMtYSRaDeIXtnQ /oU+62CMXBUhbSf6doD5+iR7ncuhuJ3tgce+HAh2dHbXWP5OcFplsKgsrYMNh3EzO40I 6p1ABbB9IPv1X2qydoKoNQjscDQ2eDY1WqrJs= Received: by 10.82.136.4 with SMTP id j4mr302089bud.46.1213073895220; Mon, 09 Jun 2008 21:58:15 -0700 (PDT) Received: by 10.82.184.18 with HTTP; Mon, 9 Jun 2008 21:58:15 -0700 (PDT) Message-ID: <34d269d40806092158vc3069e7gfd58166dee46aa2c@mail.gmail.com> Date: Mon, 9 Jun 2008 22:58:15 -0600 From: "Alex Hunsaker" To: "Tommy Cheng" Subject: Re: turn off auto index for foreign key Cc: pgsql-admin@postgresql.org In-Reply-To: <2267dd9e0806092117sda112dt8ac2f2ba8ed5e66f@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <2267dd9e0806091948k24a46cegcd45e65a5aa632ed@mail.gmail.com> <2267dd9e0806092117sda112dt8ac2f2ba8ed5e66f@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200806/65 X-Sequence-Number: 29403 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)