I'm attempting to create a Foreign Key Constraint between a table and a
view in PostgreSQL 8.3. Ex:
ALTER TABLE public.table ADD CONSTRAINT table_to_view_fk
FOREIGN KEY (view_key)
REFERENCES public.view (view_key)
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE;
However, I get an error about public.view not being a table. I know in
other RDBMS (Oracle) I can specify a constraint on a view, with limited
functionality.
The problem I'm specifically trying to solve is a typical Data
Warehousing problem where I have a "Fact" that requires two instances of
a Foreign Key join on a Time Dimension. So, the solution that was
suggested to me is to make two views of the Time Dimension that will then
be joined to the Fact table (giving unique fact specfic names in the
view definition for the constraints) I know I could just make a copy of
the time dimension, but it seems like a more elegant solution must be
available.
--
--Christopher Brodt
