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.
