Обсуждение: Automaticly delete related data
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
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.
-----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-----