Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from
	<pgsql-sql-owner+M37314=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
	id 1Uhj0x-0001Iy-Sk
	for pgsql-sql@arkaria.postgresql.org; Wed, 29 May 2013 16:14:16 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
	by malur.postgresql.org with smtp (Exim 4.72) (envelope-from
	<pgsql-sql-owner+M37314=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
	id 1Uhj0x-0004Cm-9x
	for pgsql-sql@arkaria.postgresql.org; Wed, 29 May 2013 16:14:15 +0000
Received: from makus.postgresql.org ([2001:4800:7903:4::125])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <rr.rosas@gmail.com>)
	id 1Uhj0v-0004B7-3i; Wed, 29 May 2013 16:14:13 +0000
Received: from mail-yh0-x22c.google.com ([2607:f8b0:4002:c01::22c])
	by makus.postgresql.org with esmtp (Exim 4.80)
	(envelope-from <rr.rosas@gmail.com>)
	id 1Uhj0o-0004Qe-J5; Wed, 29 May 2013 16:14:12 +0000
Received: by mail-yh0-f44.google.com with SMTP id 29so859814yhl.3
	for <multiple recipients>; Wed, 29 May 2013 09:14:05 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
	h=message-id:date:from:user-agent:mime-version:to:cc:subject
	:references:in-reply-to:content-type;
	bh=o+P8bE8TSE4l+WvDDzVdx9XOMGT6yEpXxv7PuBwvqww=;
	b=OUfBThuPa3vP0MdM8FVc3VD+24yJjASmlQNw/ULYtvMBqmtxtNKCHg3nTmNojQL+Z0
	uzJBakrPm24FgcauuMLJRVN76mK+idQRtGTEA17Y8FH0l4fjfPEdysyYVs9W0vSrqNhV
	3rnHchXInsEqf5JPRGCogtmgsBtRFE111iziYV8Ju7+GGaoCcrvwijDMow67JlBhFgl9
	M6zY4oq4hF5O9fshEObsacHb4uWqLpEakmWQwD68Bl4r9/IShq2TTQstiUC52+KoeW+o
	UnKaGZn7j/z8qwcDXpb8UsnOk2QX+/jFQY2+obF3MRK8EcNyLu1gVa9H2+fN9lPzBRDL
	xWhQ==
X-Received: by 10.236.112.14 with SMTP id x14mr1399304yhg.59.1369844045033;
	Wed, 29 May 2013 09:14:05 -0700 (PDT)
Received: from [192.168.2.3] ([179.217.1.154]) by mx.google.com with ESMTPSA
 id
	v27sm53262542yhj.12.2013.05.29.09.14.02 for <multiple recipients>
	(version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128);
	Wed, 29 May 2013 09:14:04 -0700 (PDT)
Message-ID: <51A62948.4040603@gmail.com>
Date: Wed, 29 May 2013 13:14:00 -0300
From: Rodrigo Rosenfeld Rosas <rr.rosas@gmail.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:17.0) Gecko/20130518 Icedove/17.0.5
MIME-Version: 1.0
To: Vick Khera <vivek@khera.org>
CC: pgsql-sql@postgresql.org,
 pgsql-general <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] foreign key to multiple tables depending on another
	column's value
References: <51A60971.8060608@gmail.com>
	<CALd+dcdyGn12Fbmyhpau6JvfB3MiiiYvXCH2WbFWFHoSN-P7Lg@mail.gmail.com>
In-Reply-To: 
 <CALd+dcdyGn12Fbmyhpau6JvfB3MiiiYvXCH2WbFWFHoSN-P7Lg@mail.gmail.com>
Content-Type: multipart/alternative;
	boundary="------------000003080701060804060007"
X-Pg-Spam-Score: 0.7 (/)
List-Archive: <http://archives.postgresql.org/pgsql-sql>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-sql.postgresql.org>
List-Owner: <mailto:pgsql-sql-owner@postgresql.org>
List-Post: <mailto:pgsql-sql@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-sql>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
X-Mailing-List: pgsql-sql
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

This is a multi-part message in MIME format.
--------------000003080701060804060007
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Em 29-05-2013 12:51, Vick Khera escreveu:
>
> On Wed, May 29, 2013 at 9:58 AM, Rodrigo Rosenfeld Rosas 
> <rr.rosas@gmail.com <mailto:rr.rosas@gmail.com>> wrote:
>
>     I know I could use a trigger, or some check constraint maybe, to
>     ensure the field exists upon insert (or update), but I can't
>     ensure the database will become inconsistent in case I remove a
>     mapped field from the other schema.
>
>     Now I can finally explain my question: is it possible that I set
>     some sort of foreign key whose referenced table and column would
>     depend on the value of another column?
>
>
> The FK tests are basically triggers, but highly optimized.
>
> That said, the way they enforce the integrity is by having a trigger 
> on both tables. So for your custom need here, you would want to put a 
> trigger on the referenced table to disallow deleting a value that is 
> still referenced, or do whatever appropriate action upon delete/update 
> your application needs.
>

Ok, thanks. I just wanted to be sure there wasn't some hidden feature of 
PostgreSQL I wasn't aware of yet...

You know, I'm always learning something new on PG, so it worths trying 
to ask first ;)

Cheers,
Rodrigo.


--------------000003080701060804060007
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<html>
  <head>
    <meta content="text/html; charset=ISO-8859-1"
      http-equiv="Content-Type">
  </head>
  <body text="#000000" bgcolor="#FFFFFF">
    <div class="moz-cite-prefix">Em 29-05-2013 12:51, Vick Khera
      escreveu:<br>
    </div>
    <blockquote
cite="mid:CALd+dcdyGn12Fbmyhpau6JvfB3MiiiYvXCH2WbFWFHoSN-P7Lg@mail.gmail.com"
      type="cite">
      <div dir="ltr">
        <div class="gmail_extra"><br>
          <div class="gmail_quote">On Wed, May 29, 2013 at 9:58 AM,
            Rodrigo Rosenfeld Rosas <span dir="ltr">&lt;<a
                moz-do-not-send="true" href="mailto:rr.rosas@gmail.com"
                target="_blank">rr.rosas@gmail.com</a>&gt;</span> wrote:<br>
            <blockquote class="gmail_quote" style="margin:0 0 0
              .8ex;border-left:1px #ccc solid;padding-left:1ex">
              <div id=":9p" style="overflow:hidden">I know I could use a
                trigger, or some check constraint maybe, to ensure the
                field exists upon insert (or update), but I can't ensure
                the database will become inconsistent in case I remove a
                mapped field from the other schema.<br>
                <br>
                Now I can finally explain my question: is it possible
                that I set some sort of foreign key whose referenced
                table and column would depend on the value of another
                column?<br>
              </div>
            </blockquote>
          </div>
          <br>
          The FK tests are basically triggers, but highly optimized.</div>
        <div class="gmail_extra"><br>
        </div>
        <div class="gmail_extra">That said, the way they enforce the
          integrity is by having a trigger on both tables. So for your
          custom need here, you would want to put a trigger on the
          referenced table to disallow deleting a value that is still
          referenced, or do whatever appropriate action upon
          delete/update your application needs.</div>
        <div class="gmail_extra"><br>
        </div>
      </div>
    </blockquote>
    <br>
    Ok, thanks. I just wanted to be sure there wasn't some hidden
    feature of PostgreSQL I wasn't aware of yet...<br>
    <br>
    You know, I'm always learning something new on PG, so it worths
    trying to ask first ;)<br>
    <br>
    Cheers,<br>
    Rodrigo.<br>
    <br>
  </body>
</html>

--------------000003080701060804060007--