From: Matthias Nagel <matthias.h.nagel@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Restrict FOREIGN KEY to a part of the referenced table
Date: Thu, 11 Apr 2013 09:55:20 +0200
Message-ID: <3358559.iSkkklqms2@hek506> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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?
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:
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 ) REFERENCES parent ( 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,
attribute2 VARCHAR,
...,
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 constant value for the sole purpose to be able to use this column in the FOREIGN KEY clause. On the one hand this is a waste of memory and on the other hand it is not immediately obvious to an outside person what the purpose of this extra column and CHECK clause is. I am convinced that any administrator who follows me might get into problems to understand what this 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
--
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: matthias.h.nagel@gmail.com
Subject: Re: Restrict FOREIGN KEY to a part of the referenced table
In-Reply-To: <3358559.iSkkklqms2@hek506>
* 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