Re: Automaticly delete related data

-----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(at)xyzzy(dot)dhs(dot)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-----