Path: news.easynews.com!core-easynews!newsfeed1.easynews.com!easynews.com!easynews!small1.nntp.aus1.giganews.com!border1.nntp.aus1.giganews.com!nntp.giganews.com!nntp-relay.ihug.net!ihug.co.nz!news-out.newsfeeds.com!propagator2-maxim!news-in.superfeed.net!mozo.cc.purdue.edu!not-for-mail From: "Oleg Fedorov" Newsgroups: comp.databases.oracle.misc Subject: Re: Foreign key reference to a view column Date: Mon, 5 May 2003 13:54:15 -0500 Organization: Purdue University Lines: 76 Message-ID: References: <28745N450@web2news.com> NNTP-Posting-Host: dyn-167-030.admin.purdue.edu X-Trace: mozo.cc.purdue.edu 1052160859 28866 128.210.167.30 (5 May 2003 18:54:19 GMT) X-Complaints-To: news@news.purdue.edu NNTP-Posting-Date: Mon, 5 May 2003 18:54:19 +0000 (UTC) X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2800.1106 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 Xref: core-easynews comp.databases.oracle.misc:97335 X-Received-Date: Mon, 05 May 2003 11:59:39 MST (news.easynews.com) Hi Gian, Since the other tables that refered to the original customer table still have a foreign key reference on the customer number you do not need to simulate a foreign key referencing a view column. See an example below. SQL> create table a(id number primary key); Table created. SQL> create table b(fk number referencing a); Table created. SQL> alter table a add( 2 ver number); Table altered. SQL> create view a_view as select id,ver from a where ver=1; View created. SQL> insert into a(id,ver) values(100,1); 1 row created. SQL> insert into b(fk) values(100); 1 row created. SQL> insert into b(fk) values(101); insert into b(fk) values(101) * ERROR at line 1: ORA-02291: integrity constraint (OFEDOROV.SYS_C001070) violated - parent key not found We was not able to inser a "wrong" record. Good luck. Oleg "GFC" wrote in message news:28745N450@web2news.com... > Hello, > > Can someone help me with the following: > > I recently had to modify a customer table by adding a version column > next to each customer number. This to allow tracking changes of customer > information. > > Other tables refered to the original customer table with a foreign key > reference on the customer number. > > Now I have made a view on the new customer table so that only the > highest customer version is shown, thereby simulating the original > functionality. > > The problem is that the original foreign key constraint cannot be used > on the view so my application can't validate customer numbers anymore. > > How can I simulate a foreign key referencing a view column? > > Has anyone encountered a similar problem? > > Thanks, > > Gian Franco > -- > Direct access to this group with http://web2news.com > http://web2news.com/?comp.databases.oracle.misc