Create Foreign Key Constraint on a View

From: Christopher Brodt <chris(at)uberbrodt(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Create Foreign Key Constraint on a View
Date: 2009-06-04 20:18:39
Message-ID: h09a6v$pb1$1@news.eternal-september.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


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

Browse pgsql-general by date

  From Date Subject
Next Message Brandon Metcalf 2009-06-04 20:23:28 Re: limit table to one row
Previous Message Richard Broersma 2009-06-04 20:17:49 Re: limit table to one row