Try using triggers: (see attached example)
José
Pablo Sentis ha scritto:
> Part 1.1 Type: Plain Text (text/plain)
> Encoding: quoted-printable
--Referential integrity:
--1. Don't allow to add a detail without header
--2. Delete all details in cascade if one decide to delete the header
--3. Update details' key in cascade if header's key is changed
DROP TABLE header;
CREATE TABLE header (
year integer NOT NULL,
number INTEGER NOT NULL,
date DATE NOT NULL,
cod_client CHAR(4) NOT NULL,
CONSTRAINT k_header PRIMARY KEY (year,number)
);
DROP TABLE detail;
CREATE TABLE detail (
year integer NOT NULL,
number INTEGER NOT NULL,
cod_product CHAR(05) NOT NULL,
qty INTEGER NOT NULL,
cost DECIMAL(8,2),
CONSTRAINT k_detail PRIMARY KEY (year,number,cod_product),
CONSTRAINT k_extern FOREIGN KEY(year,number) references HEADER
);
drop function f_not_add_detail();
create function f_not_add_detail() returns opaque as '
declare
/* to avoid insert detail if header doesn''t exist */
tot int;
begin
select number into tot from header
where year = new.year and number = new.number;
if not found then
raise notice ''I cannot add a detail without header!'';
return NULL;
else
return new;
end if;
end;
' language 'plpgsql';
drop function f_del_detail();
create function f_del_detail() returns opaque as '
begin
-- Delete details in cascade...
delete from detail where
detail.year = old.year
and detail.number = old.number;
return new;
end;
' language 'plpgsql';
drop function f_upd_detail();
create function f_upd_detail() returns opaque as '
begin
-- Updates details keys in cascade...
update detail set year = new.year, number = new.number
where detail.year = old.year
and detail.number = old.number;
return new;
end;
' language 'plpgsql';
create trigger t_ins_after before INSERT
on detail for each row execute procedure f_not_add_detail();
create trigger t_del_after after DELETE
on header for each row execute procedure f_del_detail();
create trigger t_upd_after after UPDATE
on header for each row execute procedure f_upd_detail();
insert into header values(1999,321,current_date,'C128');
insert into detail values(1999,321,'B139',2,200.35);
insert into header values(1997,132,current_date,'C500');
insert into detail values(1997,132,'B166',3,120.50);
select * from header;
select * from detail;
update header set year=1998 where year=1999;
select * from header;
select * from detail;
delete from header where year=1998;
select * from header;
select * from detail;
insert into detail values(1999,321,'B139',2,200.35);
