Received: from xyzzy.lan.internal (d226-80-2.home.cgocable.net [24.226.80.2]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fAPA6Um95961 for ; Sun, 25 Nov 2001 05:06:30 -0500 (EST) (envelope-from drew@xyzzy.dhs.org) Received: from localhost ([127.0.0.1] helo=there ident=drew) by xyzzy.lan.internal with smtp (Exim 3.32 #1 (Debian)) id 167wB6-0006NK-00; Sun, 25 Nov 2001 05:06:24 -0500 Content-Type: text/plain; charset="iso-8859-1" From: "Andrew G. Hammond" To: Trond Arve Nordheim , pgsql-sql@postgresql.org Subject: Re: Automaticly delete related data Date: Sun, 25 Nov 2001 05:06:17 -0500 X-Mailer: KMail [version 1.3.2] References: <1006654229.11145.8.camel@geekpower> In-Reply-To: <1006654229.11145.8.camel@geekpower> MIME-Version: 1.0 Message-Id: Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by postgresql.org id fAPADfL96065 X-Archive-Number: 200111/356 X-Sequence-Number: 5738 -----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-----