Re: Foreign Key to an (abstract?) Parent Table

Hi,
You are right - foregin keys are not inherited (doc:
http://www.postgresql.org/docs/9.1/static/ddl-inherit.html).

hi
As work around You may crate similar structure of inherited tables
structure for "seen_its" (with no additional columns). Every table should
correspond to the table in "moments" structure.
Then You may implement foreign keys for corresponded tables. In that way
updates and deletes will work correctly. So, You will have two branches of
inherited structures.

Hope this helps.

Regards,
Bartek

2012/2/18 Alessandro Gagliardi <alessandro(at)path(dot)com>

> I have
> CREATE TABLE moments
> (
> moment_id character(24) NOT NULL DEFAULT to_char(now(),
> 'JHH24MISSUS'::text),
> block_id character(24) NOT NULL,
> inserted timestamp without time zone NOT NULL DEFAULT now(),
> CONSTRAINT moments_pkey PRIMARY KEY (moment_id )
> )
> which is the parent of a bunch of other tables like
> CREATE TABLE music
> (
> track_id character(24),
> CONSTRAINT music_pkey PRIMARY KEY (moment_id )
> )
> INHERITS (moments)
> and
> CREATE TABLE thoughts
> (
> body text,
> CONSTRAINT thought_pkey PRIMARY KEY (moment_id )
> )
> INHERITS (moments)
> In fact, the moments table itself is empty. (In OOP terms, I suppose it
> would be "abstract".) However, moment_id should be unique across all of
> the tables that inherit moments.
> But this question isn't about inheriting a primary key (which I know
> PostgreSQL doesn't do). Rather, it is about creating a foreign key. You
> see, I also have
> CREATE TABLE seen_its
> (
> user_id character(24) NOT NULL,
> moment_id character(24) NOT NULL,
> created timestamp without time zone,
> inserted timestamp without time zone DEFAULT now(),
> CONSTRAINT seen_its_pkey PRIMARY KEY (user_id , moment_id )
> )
> and what I would like is for moment_id in seen_its to be a foreign key
> that applies to all of the tables that inherit from moments. I tried
> ALTER TABLE seen_its ADD CONSTRAINT seen_it_moment_id FOREIGN KEY
> (moment_id) REFERENCES moments (moment_id)
> ON UPDATE NO ACTION ON DELETE NO ACTION;
> CREATE INDEX fki_seen_it_moment_id ON seen_its(moment_id);
> but, as you might imagine, it fails because none of the moment_ids in
> seen_its are in moments, they're in music and thoughts and so on.
> Is there any way to make this work?
> Thank you in advance!
> -Alessandro Gagliardi
>
>