Re: Restrict FOREIGN KEY to a part of the referenced table

On Thu, Apr 11, 2013 at 12:55 AM, Matthias Nagel <matthias(dot)h(dot)nagel(at)gmail(dot)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.