Re: Restrict FOREIGN KEY to a part of the referenced table

On 2013-04-11, Matthias Nagel  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