Re: Inherited FK Indexing

On 9/14/07, Ketema Harris <ketema(at)ketema(dot)net> wrote:
> I have the following table set up:
>
> CREATE TABLE states
> (
> state_id integer NOT NULL DEFAULT nextval
> ('state_province_id_seq'::regclass),
> state character(2),
> full_name character varying,
> timezone character varying,
> CONSTRAINT "PK_state_id" PRIMARY KEY (state_id)
> )
>
> CREATE TABLE canadian_provinces
> (
> -- Inherited: state_id integer NOT NULL DEFAULT nextval
> ('state_province_id_seq'::regclass),
> -- Inherited: state character(2),
> -- Inherited: full_name character varying,
> -- Inherited: timezone character varying,
> CONSTRAINT "PK_province_id" PRIMARY KEY (state_id)
> )
>
> as expected I can do select * from states and get everything out of
> the child table as well. What I can't do is create a FK to the
> states table and have it look in the child table as well. Is this on
> purpose?

Not so much on purpose as an artifact of the design process.
PostgreSQL can't span multiple tables with indexes, a unique one of
which is required for a FK to point to a field.

> Is it possible to have FK that spans into child tables?

Not really. You might be able to write your own function that
approximates such behavior. I would think some kind of intermediate
table with every value from all the children for that one column could
be used, but performance would suffer.