Re: Can Foreign Key Constraint be based on a view?

From: Jusung Yang <JusungYang_at_yahoo.com>
Date: 15 Apr 2003 10:25:36 -0700
Message-ID: <130ba93a.0304150925.2da994bf_at_posting.google.com>


Though you can create FK constraint on a table that references a view which has primary key constraint, both must be in disable, novalidate state and can not be used for enforcement and validation. So, basically what you are asking for can not be done.

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&selm=130ba93a.0303181053.18eb294b%40posting.google.com

  • Jusung Yang

"John" <John_at_none_zzzz.com> wrote in message news:<fpIma.108589$yh1.6112273_at_news1.east.cox.net>...
> Hi all,
>
> Can Foreign Key Constraint be based on a view?
>
> Example:
>
> I have a master table that lists a number of projects.
>
> table: proj
> -----------
> project_id not null varchar2(10) primary key
> project_name not null varchar2(40)
> project_type not null varchar2(1)
> ...
>
>
> I want to allow a developer to populate a secondary table that allows
> a subset of rows of the master table. This seconday table is proj_budget
> listed below.
>
> table: proj_budget
> ------------------
> fiscal_year not null varchar2(4)
> project_id not null varchar2(10)
> project_budg number(12,2)
> ...
>
>
> The subset to be allowed can be defined using the following view.
>
> create view as proj_amt_list as (select * from proj where project_type='P');
>
>
>
> How would one define the foreign key constraint for proj_budget to allow
> only the projects included in the view?
>
> Thanks in advance.
Received on Tue Apr 15 2003 - 19:25:36 CEST

Original text of this message