pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Andrew G. Hammond <drew@xyzzy.dhs.org>
To: Trond Arve Nordheim <trond@nordheim.no>
To: pgsql-sql@postgresql.org
Subject: Re: Automaticly delete related data
Date: Sun, 25 Nov 2001 05:06:17 -0500
Message-ID: <E167wB6-0006NK-00@xyzzy.lan.internal> (raw)
In-Reply-To: <1006654229.11145.8.camel@geekpower>
References: <1006654229.11145.8.camel@geekpower>

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



view thread (3+ messages)

Message-ID: <E167wB6-0006NK-00@xyzzy.lan.internal>
Permalink:  ../E167wB6-0006NK-00@xyzzy.lan.internal/
Also on:    postgresql.org/message-id/E167wB6-0006NK-00@xyzzy.lan.internal

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: drew@xyzzy.dhs.org, trond@nordheim.no
  Subject: Re: Automaticly delete related data
  In-Reply-To: <E167wB6-0006NK-00@xyzzy.lan.internal>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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