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