pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Restrict FOREIGN KEY to a part of the referenced table 3+ messages / 3 participants [nested] [flat]
* Restrict FOREIGN KEY to a part of the referenced table @ 2013-04-11 07:55 Matthias Nagel <matthias.h.nagel@gmail.com> 0 siblings, 2 replies; 3+ messages in thread From: Matthias Nagel @ 2013-04-11 07:55 UTC (permalink / raw) To: pgsql-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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Restrict FOREIGN KEY to a part of the referenced table @ 2013-04-12 13:53 Jasen Betts <jasen@xnet.co.nz> parent: Matthias Nagel <matthias.h.nagel@gmail.com> 1 sibling, 0 replies; 3+ messages in thread From: Jasen Betts @ 2013-04-12 13:53 UTC (permalink / raw) To: pgsql-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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Restrict FOREIGN KEY to a part of the referenced table @ 2013-04-12 19:52 Richard Broersma <richard.broersma@gmail.com> parent: Matthias Nagel <matthias.h.nagel@gmail.com> 1 sibling, 0 replies; 3+ messages in thread From: Richard Broersma @ 2013-04-12 19:52 UTC (permalink / raw) To: Matthias Nagel <matthias.h.nagel@gmail.com>; +Cc: pgsql-sql On Thu, Apr 11, 2013 at 12:55 AM, Matthias Nagel <matthias.h.nagel@gmail.com > wrote: > > 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. True. 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. > If you need to improve documentation for this you have two options that can help the future admin: replace: CHECK ( parent_discriminator = 42 ) with: CONSTRAINT "These children only like parents of type 42" CHECK( parent_discriminator = 42) or: COMMENT ON TABLE child "Your explanation goes here."; If you need to hide this column from you uses, you can use a view. -- Regards, Richard Broersma Jr. ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2013-04-12 19:52 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2013-04-11 07:55 Restrict FOREIGN KEY to a part of the referenced table Matthias Nagel <matthias.h.nagel@gmail.com> 2013-04-12 13:53 ` Jasen Betts <jasen@xnet.co.nz> 2013-04-12 19:52 ` Richard Broersma <richard.broersma@gmail.com>
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