How to do a constraint to reference a view?

Hi all, I'm trying to create a reference to a view from a table, but I couldn't do it. I mean I'm tryin to put a restriction on table that when I insert some data int it, IDS validates that this data exist in a view. I tryed to create a foreign key referencing the view but IDS says that he can't encounter a unique or primary key on the referencing object. the view. Then I tryed to create an index on the view but it fails: this is not supported or logical of course; so the foerign key approach doesn't work. A way of doing this is by creating triggers on the table where I want to insert data and then do the validation for myself in a stored procedure but I don't want to use this approach, I mean I want IDS to check this. Could this be done ? Any hint other than te trigger/sp approach ? J. Jean Sagi jeansagi@myrealbox.com jeansagi@gmail.com

If the values exist in the view then they exist in the underlying table(s) as well, unless they are computed. So, I'm thinking that you should be able to create FK constraints from the insert table to the table(s) being referenced by the view. If your FK requirements are more elaborate than that I'm inclined to suggest that you review your database architecture and change it so that referential integrity does not need to depend on a view. it might help us help you if you could be more specific with the RI requirements and post the DDL for the view and columns then you wish to reference the view. --- Jean Sagi <jeansagi@myrealbox.com> wrote: > Hi all, > > I'm trying to create a reference to a view from a table, but I > couldn't do it. > > I mean I'm tryin to put a restriction on table that when I insert > some data int it, IDS validates that this data exist in a view. > > I tryed to create a foreign key referencing the view but IDS says > that he can't encounter a unique or primary key on the referencing > object. the view. > > Then I tryed to create an index on the view but it fails: this is not > supported or logical of course; so the foerign key approach doesn't > work. > > A way of doing this is by creating triggers on the table where I want > to insert data and then do the validation for myself in a stored > procedure but I don't want to use this approach, I mean I want IDS to > check this. > > Could this be done ? > > Any hint other than te trigger/sp approach ? > > > J. > > Jean Sagi > jeansagi@myrealbox.com > jeansagi@gmail.com

In essence, you have answered your own question. Because you cannot add an index to the view, you cannot add a constraint, which depends on the index, to the view. What you have left to do is reference the underlying table. If the view is on a join, or does some manipulation of the data and you are trying to reference computed data, all that remains is the trigger route, really. Sincerely, Christopher Coleman Steering Committee President Kansas City Informix Users Group www.iiug.org/kciug Database Analyst Pharmacy Division Mediware Information Systems, Inc. -----Original Message----- From: Jean Sagi [mailto:jeansagi@myrealbox.com] Sent: Thursday, December 15, 2005 3:00 PM To: ids@iiug.org Subject: How to do a constraint to reference a view? [6117] Hi all, I'm trying to create a reference to a view from a table, but I couldn't do it. I mean I'm tryin to put a restriction on table that when I insert some data int it, IDS validates that this data exist in a view. I tryed to create a foreign key referencing the view but IDS says that he can't encounter a unique or primary key on the referencing object. the view. Then I tryed to create an index on the view but it fails: this is not supported or logical of course; so the foerign key approach doesn't work. A way of doing this is by creating triggers on the table where I want to insert data and then do the validation for myself in a stored procedure but I don't want to use this approach, I mean I want IDS to check this. Could this be done ? Any hint other than te trigger/sp approach ? J. Jean Sagi jeansagi@myrealbox.com jeansagi@gmail.com

Certainly it seems there is no way to what I want to do but using trigges/sp's as you sugested. Reading the manuals I thought that I could use a CHECK constraint to do this but unfortunatly a check can't use a subquery (But by following syntax diagrams you can do it ;) Although the manual explicitly prohibit them). As you suposed the view is a join between two tables so things get complicated. The background on this is that I have the following schema: | G |--0..1-----0..n--| A | | A |--1..1-----1..n--| AP | | P |--1..1-----1..n--| AP | So that a relatioin exists between |G| and |P|, which could be expressed by a view |GP|, so no need to create a table to phisically represent this relationship. Now years after ( ;) ) there is a new business rule (R) that logically could be implemented by a relation between |GP| and itself. (R) | GP |--1..1-----0..n--| GP | So I wanted to create a table |H| which implement these relationships: | GP |--1..1-----0..n--| H | | GP |--1..1-----0..n--| H | This could be perfectly be done with a foreign key. The this is that |GP| is a view. Anyway, thanks for your sugestions. J. -----Original Message----- From: "Christopher Coleman" <Christopher.Coleman@mediware.com> To: "Jean Sagi" <jeansagi@myrealbox.com>, <ids@iiug.org> Date: Thu, 15 Dec 2005 17:27:34 -0600 Subject: RE: How to do a constraint to reference a view? [6117] In essence, you have answered your own question. Because you cannot add an index to the view, you cannot add a constraint, which depends on the index, to the view. What you have left to do is reference the underlying table. If the view is on a join, or does some manipulation of the data and you are trying to reference computed data, all that remains is the trigger route, really. Sincerely, Christopher Coleman Steering Committee President Kansas City Informix Users Group www.iiug.org/kciug Database Analyst Pharmacy Division Mediware Information Systems, Inc. -----Original Message----- From: DL Redden <redden96@yahoo.com> To: Jean Sagi <jeansagi@myrealbox.com>, ids@iiug.org Date: Thu, 15 Dec 2005 14:26:31 -0800 (PST) Subject: Re: How to do a constraint to reference a view? [6117] If the values exist in the view then they exist in the underlying table(s) as well, unless they are computed. So, I'm thinking that you should be able to create FK constraints from the insert table to the table(s) being referenced by the view. If your FK requirements are more elaborate than that I'm inclined to suggest that you review your database architecture and change it so that referential integrity does not need to depend on a view. it might help us help you if you could be more specific with the RI requirements and post the DDL for the view and columns then you wish to reference the view. -----Original Message----- From: Jean Sagi [mailto:jeansagi@myrealbox.com] Sent: Thursday, December 15, 2005 3:00 PM To: ids@iiug.org Subject: How to do a constraint to reference a view? [6117] Hi all, I'm trying to create a reference to a view from a table, but I couldn't do it. I mean I'm tryin to put a restriction on table that when I insert some data int it, IDS validates that this data exist in a view. I tryed to create a foreign key referencing the view but IDS says that he can't encounter a unique or primary key on the referencing object. the view. Then I tryed to create an index on the view but it fails: this is not supported or logical of course; so the foerign key approach doesn't work. A way of doing this is by creating triggers on the table where I want to insert data and then do the validation for myself in a stored procedure but I don't want to use this approach, I mean I want IDS to check this. Could this be done ? Any hint other than te trigger/sp approach ? J. Jean Sagi jeansagi@myrealbox.com jeansagi@gmail.com Jean Sagi jeansagi@myrealbox.com jeansagi@gmail.com