[GENERAL] Inherited FK Indexing
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? Is it possible to have FK that spans into child tables?
Thanks
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] Inherited FK Indexing
On 9/14/07, Ketema Harris <[EMAIL PROTECTED]> 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.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Inherited FK Indexing
On Friday 14 September 2007, Ketema Harris <[EMAIL PROTECTED]> wrote: > 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? Is it possible to have FK that spans into child tables? No. -- The only difference between conservatives and liberals regarding budget cuts is tense. Conservatives say they will cut the budget, and then they increase it. After the budget has increased, liberals say that it has been cut. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Inherited FK Indexing
On Sep 14, 2007, at 10:35 AM, Ketema Harris 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? Is it possible to have FK that spans into child tables?
I'm assuming you just left out an INHERITS clause or ALTER TABLE
statement to add the inheritance? Anyways, the answer to your
question is no, you'll need to create any dependencies to child
tables separately.
Erik Jones
Software Developer | Emma®
[EMAIL PROTECTED]
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] Inherited FK Indexing
Hi, Ketema Harris wrote: 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? Is it possible to have FK that spans into child tables? This is a well known (and documented, see [1]) deficiency. It's due to the current implementation of indices, which are bound to exactly one table, meaning they do return a position within the table, but cannot point to different tables. Regards Markus [1]: Postgres Documentation, Chapter 5.8.1 Caveats (of Inheritance): http://www.postgresql.org/docs/8.2/static/ddl-inherit.html ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
