From: Jasen Betts <jasen@xnet.co.nz>
To: pgsql-sql@postgresql.org
Subject: Re: Restrict FOREIGN KEY to a part of the referenced table
Date: 12 Apr 2013 13:53:19 GMT
Message-ID: <kk93kf$d66$1@gonzo.reversiblemaps.ath.cx> (raw)
References: <3358559.iSkkklqms2@hek506>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
On 2013-04-11, Matthias Nagel <matthias.h.nagel@gmail.com> wrote:
> Hello,
>
> is there any best practice method how to create a foreign key that only allows values from those rows in the referenced table that fulfill an additional condition?
tes. make the key wide enough to capture this state.
I dom't like it either.
or partition the child table (which may not work for other constraints)
> First I present two pseudo solutions to clarify what I would like to
> do. They are no real solutions, because they are neither SQL standard
> nor postgresql compliant. The third solution actually works, but I do
> not like it for reason I will explain later:
> FOREIGN KEY ( parent_id, 42 ) REFERENCES parent ( id, discriminator )
I have wanted this before too.
> FOREIGN KEY ( parent_id ) REFERENCES ( SELECT * FROM parent WHERE discri
I hadn't thought of expressing it like that. or similarly using a view
instead of a select.
but I think I have tried
FOREIGN KEY ( parent_id ) REFERENCES parent ( id ) where discriminator = 42
and it didn't work.
--
⚂⚃ 100% natural
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jasen@xnet.co.nz
Subject: Re: Restrict FOREIGN KEY to a part of the referenced table
In-Reply-To: <kk93kf$d66$1@gonzo.reversiblemaps.ath.cx>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox