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

Hello,

is there any best practice method how to create a foreign key that only allows values from those rows in the referenced
tablethat fulfill an additional condition?
 

First I present two pseudo solutions to clarify what I would like to do. They are no real solutions, because they are
neitherSQL standard nor postgresql compliant. The third solution actually works, but I do not like it for reason I will
explainlater:
 

CREATE TABLE parent ( id SERIAL, discriminator INT NOT NULL, attribute1 VARCHAR, ...
);


Pseudo solution 1 (with a hard-coded value):

CREATE TABLE child ( id SERIAL NOT NULL, parent_id INT NOT NULL, attribute2 VARCHAR, ..., FOREIGN KEY ( parent_id, 42 )
REFERENCESparent ( id, discriminator )
 
);


Pseudo solution 2 (with a nested SELECT statement):

CREATE TABLE child ( id SERIAL NOT NULL, parent_id INT NOT NULL, attribute2 VARCHAR, ..., FOREIGN KEY ( parent_id )
REFERENCES( SELECT * FROM parent WHERE discriminator = 42 ) ( id )
 
);


Working solution:

CREATE TABLE child ( id SERIAL NOT NULL, parent_id INT NOT NULL, parent_discriminator INT NOT NULL DEFAULT 42,
attribute2VARCHAR, ..., FOREIGN KEY ( parent_id, parent_discriminator ) REFERENCES parent ( id, discriminator ), CHECK
(parent_discriminator = 42 )
 
);


The third solution work, but I do not like it, because it adds an extra column to the table that always contains a
constantvalue for the sole purpose to be able to use this column in the FOREIGN KEY clause. On the one hand this is a
wasteof memory and on the other hand it is not immediately obvious to an outside person what the purpose of this extra
columnand CHECK clause is. I am convinced that any administrator who follows me might get into problems to understand
whatthis is supposed to be. I would like to have a more self-explanatory solution like 1 or 2.
 

I wonder if there is something better.

Best regards, Matthias

----------------------------------------------------------------------
Matthias Nagel
Willy-Andreas-Allee 1, Zimmer 506
76131 Karlsruhe

Telefon: +49-721-8695-1506
Mobil: +49-151-15998774
e-Mail: matthias.h.nagel@gmail.com
ICQ: 499797758
Skype: nagmat84

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
referencedtable 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