Обсуждение: 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.
Вложения
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.