Re: [GENERAL] Referential integrity

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);