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.na...@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
