To: pgsql-sql@postgresql.org
From: Jasen Betts <jasen@xnet.co.nz>
Subject: Re: Restrict FOREIGN KEY to a part of the referenced table
Date: 12 Apr 2013 13:53:19 GMT
Organization: Dis (not Dat) Organisation
Lines: 32
Message-ID: <kk93kf$d66$1@gonzo.reversiblemaps.ath.cx>
References: <3358559.iSkkklqms2@hek506>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
User-Agent: slrn/pre1.0.0-18 (Linux)
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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