On Thu, Apr 11, 2013 at 12:55 AM, Matthias Nagel <[email protected]
> 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.
