pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Automaticly delete related data
3+ messages / 3 participants
[nested] [flat]

* Automaticly delete related data
@ 2001-11-25 02:10  Trond Arve Nordheim <trond@nordheim.no>
  0 siblings, 2 replies; 3+ messages in thread

From: Trond Arve Nordheim @ 2001-11-25 02:10 UTC (permalink / raw)
  To: pgsql-sql

Hi!

I'm developing a publishing system and are trying to create
functions/triggers that automaticly wipes out related data to the data
currently beeing deleted...

For example, I have a table containing site objects (an article or
whatever) where I have the following fields;

int_objectid SERIAL NOT NULL,
str_objecttitle VARCHAR(255) NOT NULL,
...
...

Then, I have a table defining the content of that data;
int_objectid INT(11) NOT NULL
str_data TEXT NOT NULL,
...
...

What I want to do is;
When I delete a row from the first table, I want all rows with mathing
int_objectid values in the second table to be deleted.

I've been reading in a couple of online books and the PostgreSQL
documentation all day without beeing able to make something of this...

Can anyone give me a hint, or point me in the direction of some
documentation that can help me out?

Sincerly,
Trond Arve Nordheim






^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Automaticly delete related data
@ 2001-11-25 04:06  Stephan Szabo <sszabo@megazone23.bigpanda.com>
  parent: Trond Arve Nordheim <trond@nordheim.no>
  1 sibling, 0 replies; 3+ messages in thread

From: Stephan Szabo @ 2001-11-25 04:06 UTC (permalink / raw)
  To: Trond Arve Nordheim <trond@nordheim.no>; +Cc: pgsql-sql

On 25 Nov 2001, Trond Arve Nordheim wrote:

> Hi!
>
> I'm developing a publishing system and are trying to create
> functions/triggers that automaticly wipes out related data to the data
> currently beeing deleted...
>
> For example, I have a table containing site objects (an article or
> whatever) where I have the following fields;
>
> int_objectid SERIAL NOT NULL,
> str_objecttitle VARCHAR(255) NOT NULL,
> ...
> ...
>
> Then, I have a table defining the content of that data;
> int_objectid INT(11) NOT NULL
> str_data TEXT NOT NULL,
> ...
> ...
>
> What I want to do is;
> When I delete a row from the first table, I want all rows with mathing
> int_objectid values in the second table to be deleted.
>
> I've been reading in a couple of online books and the PostgreSQL
> documentation all day without beeing able to make something of this...
>
> Can anyone give me a hint, or point me in the direction of some
> documentation that can help me out?

If you don't mind the other semantics, you can use foreign keys
to handle this for you with the on delete cascade referential action.





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Automaticly delete related data
@ 2001-11-25 10:06  Andrew G. Hammond <drew@xyzzy.dhs.org>
  parent: Trond Arve Nordheim <trond@nordheim.no>
  1 sibling, 0 replies; 3+ messages in thread

From: Andrew G. Hammond @ 2001-11-25 10:06 UTC (permalink / raw)
  To: Trond Arve Nordheim <trond@nordheim.no>; pgsql-sql

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 2001 November 24 09:10 pm, Trond Arve Nordheim wrote:

> I'm developing a publishing system and are trying to create
> functions/triggers that automaticly wipes out related data to the data
> currently beeing deleted...

This can be achieve easily using Postgres' built in referential integrity stuff.

ie:
CREATE TABLE a (id SERIAL PRIMARY KEY, title TEXT NOT NULL);
CREATE TABLE b (id SERIAL PRIMARY KEY, data TEXT NOT NULL,
  key_a INTEGER REFERENCES a(id) ON DELETE CASCADE);

INSERT INTO a(title) VALUES ('test');
INSERT INTO b(data, key_a) VALUES ('test data', (SELECT id FROM a WHERE title='test'));
SELECT * FROM b; -- should list one row.
DELETE FROM a;
SELECT * FROM b; -- should list no rows.

Take a look at the doc's for CREATE TABLE in the SQL reference.

- -- 
Andrew G. Hammond     mailto:drew@xyzzy.dhs.org   http://xyzzy.dhs.org/~drew/
56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F                  613-389-5481
5CD3 62B0 254B DEB1 86E0  8959 093E F70A B457 84B1
"To blow recursion you must first blow recur" -- me
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iEYEARECAAYFAjwAwp0ACgkQCT73CrRXhLEBdQCfXGGnjK3kMcWbT3yqyAZzaXGc
+KEAmwZyDsIOGXmBpPX50JStzaQ+be2U
=BG3D
-----END PGP SIGNATURE-----



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2001-11-25 10:06 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-11-25 02:10 Automaticly delete related data Trond Arve Nordheim <trond@nordheim.no>
2001-11-25 04:06 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-11-25 10:06 ` Andrew G. Hammond <drew@xyzzy.dhs.org>

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