Thread: RI / foreign key on VIEW
Hi all, I have a db structure with a VIEW that I need to reference (ON DELETE CASCADE). I know that it is not possible to have references on a VIEW, but maybe someone has some TRIGGERs at hand that do this job. Here's an example to play with: ------------------------------------------------------------------------------------------------ -- -- This table holds objects (many different sorts) -- CREATE TABLE objekt ( id_objekt SERIAL PRIMARY KEY, handle text ); -- -- This table makes (some) objects buyable (products) -- CREATE TABLE price ( id_price SERIAL PRIMARY KEY, id_objekt int REFERENCES objekt ON DELETE CASCADE UNIQUE NOT NULL, price float ); -- -- This table expires some products -- CREATE TABLE expire ( id_expire SERIAL PRIMARY KEY, id_objekt int REFERENCES objekt ON DELETE CASCADE UNIQUE NOT NULL, expire date NOT NULL ); -- -- This is the virtual product table -- CREATE VIEW product AS SELECT * FROM objekt JOIN price USING (id_objekt) LEFT OUTER JOIN expire USING (id_objekt) WHERE expire IS NULL OR expire > now(); INSERT INTO objekt (handle) values ('product 1'); INSERT INTO objekt (handle) values ('product 2'); INSERT INTO objekt (handle) values ('product 3'); INSERT INTO price (id_objekt, price) values (1, 1.99); INSERT INTO price (id_objekt, price) values (2, 2.99); INSERT INTO price (id_objekt, price) values (3, 3.99); INSERT INTO expire (id_objekt, expire) values (2, '01.01.1980'); -- -- This is what I need to be done with TRIGGERs -- CREATE TABLE basket ( id_basket SERIAL PRIMARY KEY, id_user int, id_objekt int REFERENCES product ON DELETE CASCADE NOT NULL ); ------------------------------------------------------------------------------------------------- ERROR: referenced relation "product" is not a table TIA -- e-Trolley Sayegh & John, Nabil Sayegh Tel.: 0700 etrolley /// 0700 38765539 Fax.: +49 69 8299381-8 PGP : http://www.e-trolley.de -- e-Trolley Sayegh & John, Nabil Sayegh Tel.: 0700 etrolley /// 0700 38765539 Fax.: +49 69 8299381-8 PGP : http://www.e-trolley.de
Nabil Sayegh wrote: > Hi all, > > I have a db structure with a VIEW that I need to reference (ON DELETE > CASCADE). > I know that it is not possible to have references on a VIEW, but maybe > someone has some TRIGGERs at hand that do this job. > > Here's an example to play with: > > ------------------------------------------------------------------------------------------------ > > -- > -- This table holds objects (many different sorts) > -- > CREATE TABLE objekt ( > id_objekt SERIAL PRIMARY KEY, > handle text > ); > > -- > -- This table makes (some) objects buyable (products) > -- > CREATE TABLE price ( > id_price SERIAL PRIMARY KEY, > id_objekt int REFERENCES objekt ON DELETE CASCADE UNIQUE NOT NULL, > price float > ); > > -- > -- This table expires some products > -- > CREATE TABLE expire ( > id_expire SERIAL PRIMARY KEY, > id_objekt int REFERENCES objekt ON DELETE CASCADE UNIQUE NOT NULL, > expire date NOT NULL > ); > > -- > -- This is the virtual product table > -- > CREATE VIEW product AS SELECT * FROM objekt JOIN price USING > (id_objekt) LEFT OUTER JOIN expire USING (id_objekt) WHERE expire IS > NULL OR expire > now(); > > INSERT INTO objekt (handle) values ('product 1'); > INSERT INTO objekt (handle) values ('product 2'); > INSERT INTO objekt (handle) values ('product 3'); > > INSERT INTO price (id_objekt, price) values (1, 1.99); > INSERT INTO price (id_objekt, price) values (2, 2.99); > INSERT INTO price (id_objekt, price) values (3, 3.99); > > INSERT INTO expire (id_objekt, expire) values (2, '01.01.1980'); > > -- > -- This is what I need to be done with TRIGGERs > -- > CREATE TABLE basket ( > id_basket SERIAL PRIMARY KEY, > id_user int, > id_objekt int REFERENCES product ON DELETE CASCADE NOT NULL > ); > > ------------------------------------------------------------------------------------------------- > > ERROR: referenced relation "product" is not a table > > TIA Last i heard VIEWs aren't yet updateable, but there are plans to make them so sometimes in the future? -- Sincerely, Reshat. ------------------------------------------------------------------------------------------- If you see my certificate with this message, you should be able to send me encrypted e-mail. Please consult your e-mail client for details if you would like to do that.
Attachment
Reshat Sabiq wrote: > > > Nabil Sayegh wrote: > >> Hi all, >> >> I have a db structure with a VIEW that I need to reference (ON DELETE >> CASCADE). >> I know that it is not possible to have references on a VIEW, but maybe >> someone has some TRIGGERs at hand that do this job. [...] > Last i heard VIEWs aren't yet updateable, but there are plans to make > them so sometimes in the future? Thanks for your answer, but I don't need updateable VIEWs. I need referenceable VIEWs. TFYH -- e-Trolley Sayegh & John, Nabil Sayegh Tel.: 0700 etrolley /// 0700 38765539 Fax.: +49 69 8299381-8 PGP : http://www.e-trolley.de
Idefix wrote: > Well, you don't need to reference the VIEW (doesn't make sense anyway) > but your OBJEKT table. The idea was that - only special objects (i.e. products) can be in the baskets - only products that aren't yet expired can be in the baskets. I agree, that basket should be an objekt, but due to compatibility reasons I'd rather leave it as a special table. > Further the Referencing should start from your OBJEKT table, so you > could cascade through all tables without any problem. >> -- >> -- This is the virtual product table >> -- >> CREATE VIEW product AS SELECT * FROM objekt JOIN price USING >> (id_objekt) LEFT OUTER JOIN expire USING (id_objekt) WHERE expire IS >> NULL OR expire > now(); TFYH -- e-Trolley Sayegh & John, Nabil Sayegh Tel.: 0700 etrolley /// 0700 38765539 Fax.: +49 69 8299381-8 PGP : http://www.e-trolley.de
On Sat, Feb 07, 2004 at 14:01:42 -0600, Reshat Sabiq <sabiq@purdue.edu> wrote: > > Last i heard VIEWs aren't yet updateable, but there are plans to make > them so sometimes in the future? You can make updateable views now using rules. There has been some talk of handling simple cases automatically.