pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to implement referential facilities in PostgreSQL
3+ messages / 3 participants
[nested] [flat]
* How to implement referential facilities in PostgreSQL
@ 1999-01-04 14:24 leif@danmos.dk
0 siblings, 0 replies; 3+ messages in thread
From: leif@danmos.dk @ 1999-01-04 14:24 UTC (permalink / raw)
To: pgsql-interfaces
Hi everybody,
Happy New Year to you all.
We have a rather large M$ Access application for time and attendance
and job time registration. It has been implemented in M$ Access using a
M$ Access database running on a M$ 'operating system'.
One of our issues is to use the PostgreSQL in stead of/underneath the
M$ Access database application, which is the existing application running
well on M$ Access with not so large amounts of data. We of course need
to run this on a Linux box. The application makes extensive use of some
Access 'referential features'.
So my question is:
What is the equivalent of
'Enforce Referential Integrity',
'Cascade Update Related Fields', and
'Cascade Update Related Records' in
SQL/PostgreSQL terms. In other words, how do I get these facilities into
my PostgreSQL database? I need to be able to have the M$ Access application
to link directly to the PostgreSQL tables using ODBC, which I have managed
to copy directly using Access, but with no Primary/Foreign keys what so ever.
Thanks for any sugestions,
Leif
^ permalink raw reply [nested|flat] 3+ messages in thread
* RE: [INTERFACES] How to implement referential facilities in Postg reSQL
@ 1999-01-04 23:47 Jackson, DeJuan <djackson@cpsgroup.com>
0 siblings, 1 reply; 3+ messages in thread
From: Jackson, DeJuan @ 1999-01-04 23:47 UTC (permalink / raw)
To: leif@danmos.dk; pgsql-interfaces
Check the contrib/spi/refint.* in the distribution. To answer your
question you use triggers to implement it. In Postgres v6.4 and higher
you could also use rules to implement referential integrity (for
examples see src/test/regress/sql/rules.sql).
-DEJ
> -----Original Message-----
> Hi everybody,
>
> Happy New Year to you all.
>
>
> We have a rather large M$ Access application for time and
> attendance
> and job time registration. It has been implemented in M$
> Access using a
> M$ Access database running on a M$ 'operating system'.
>
> One of our issues is to use the PostgreSQL in stead
> of/underneath the
> M$ Access database application, which is the existing
> application running
> well on M$ Access with not so large amounts of data. We of course need
> to run this on a Linux box. The application makes extensive
> use of some
> Access 'referential features'.
>
> So my question is:
>
> What is the equivalent of
>
> 'Enforce Referential Integrity',
> 'Cascade Update Related Fields', and
> 'Cascade Update Related Records' in
>
> SQL/PostgreSQL terms. In other words, how do I get these
> facilities into
> my PostgreSQL database? I need to be able to have the M$
> Access application
> to link directly to the PostgreSQL tables using ODBC, which I
> have managed
> to copy directly using Access, but with no Primary/Foreign
> keys what so ever.
>
>
> Thanks for any sugestions,
>
> Leif
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [INTERFACES] How to implement referential facilities in PostgreSQL
@ 1999-01-05 10:33 Jose' Soares <jose@sferacarta.com>
parent: Jackson, DeJuan <djackson@cpsgroup.com>
0 siblings, 0 replies; 3+ messages in thread
From: Jose' Soares @ 1999-01-05 10:33 UTC (permalink / raw)
To: Jackson, DeJuan <djackson@cpsgroup.com>; +Cc: leif@danmos.dk; pgsql-interfaces
(see attached file)
I have two tables HEADER and DETAIL linked by an external key
I need to have the following integrity:
1. Don't allow to insert DETAILS if doesn't exist the HEADER with the
same
key.
2. Delete in cascade all DETAILS if one cancel the HEADER having the
same key.
3. UPDATE in cascade the key of all DETAILS if one change the HEADER
key.
-Jose'-
Attachments:
trigger.sql (text/plain, 2.8K) download
DROP TABLE header;
CREATE TABLE header (
distretto CHAR(4) NOT NULL,
anno DECIMAL(4) NOT NULL,
numero INTEGER NOT NULL,
data DATE NOT NULL,
azienda CHAR(11) NOT NULL,
CONSTRAINT k_header PRIMARY KEY (distretto,anno,numero)
);
DROP TABLE detail;
CREATE TABLE detail (
distretto CHAR(4) NOT NULL,
anno DECIMAL(4) NOT NULL,
numero INTEGER NOT NULL,
cod_prestazione CHAR(05) NOT NULL,
quantita FLOAT(4) NOT NULL,
importo FLOAT(8),
CONSTRAINT k_detail PRIMARY KEY (distretto,anno,numero,cod_prestazione),
CONSTRAINT k_extern FOREIGN KEY(distretto,anno,numero) 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 numero into tot from header
where anno = new.anno and numero = new.numero;
if not found then
raise notice ''Impossible add new detail!'';
return NULL;
else
return new;
end if;
end;
' language 'plpgsql';
create trigger t_not_add_detail before insert
on detail for each row execute procedure f_not_add_detail();
--EXAMPLE:
select * from header;
select * from detail;
INSERT INTO detail VALUES ('E14','1999',2,'IMPOSSIBLE',123,345.5); --impossible
INSERT INTO header VALUES ('E14','1999',2,current_date,1235);
INSERT INTO detail VALUES ('E14','1999',2,'AB',523,35.5);
INSERT INTO header VALUES ('E14','1999',1,current_date,1235);
INSERT INTO detail VALUES ('E14','1999',1,'A1',423,45.5);
INSERT INTO detail VALUES ('E14','1999',1,'AC',123,345.5);
select * from header;
select * from detail;
drop function f_upd_key_detail();
create function f_upd_key_detail() returns opaque as '
declare
/* change in cascade the key of every detail if header key is changed */
tot int;
begin
update detail
set anno = new.anno, numero = new.numero
where anno = old.anno and numero = old.numero;
return NULL;
end;
' language 'plpgsql';
drop trigger t_upd_key_detail on header;
create trigger t_upd_key_detail after update
on header for each row execute procedure f_upd_key_detail();
--EXAMPLE:
select * from header;
select * from detail;
update header set anno='1997', numero=33 where numero = 1 and anno='1999';
select * from header;
select * from detail;
drop function f_del_cascade();
create function f_del_cascade() returns opaque as '
declare
/* cancel in cascade all details after header is deleted */
begin
delete from detail
where anno = old.anno and numero = old.numero;
return NULL;
end;
' language 'plpgsql';
drop trigger t_del_cascade on header;
create trigger t_del_cascade after delete
on header for each row execute procedure f_del_cascade();
--EXAMPLE:
select * from header;
select * from detail;
delete from header where anno = 1997;
select * from header;
select * from detail;
end of thread, other threads:[~1999-01-05 10:33 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-01-04 14:24 How to implement referential facilities in PostgreSQL leif@danmos.dk
1999-01-04 23:47 RE: [INTERFACES] How to implement referential facilities in Postg reSQL Jackson, DeJuan <djackson@cpsgroup.com>
1999-01-05 10:33 ` Jose' Soares <jose@sferacarta.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox