deleting referenced data

[Andreas Fromm]

Hi,

I have The following situation:

CREATE TABLE data1 (
id SERIAL PRIMARY KEY,
data INTEGER
);

CREATE TABLE data2 (
id SERIAL PRIMARY KEY,
data INTEGER
);

CREATE TABLE data3 (
id SERIAL PRIMARY KEY,
data INTEGER
);

CREATE TABLE relations (
id SERIAL PRIMARY KEY,
data1 INTEGER REFERENCES data1,
data2 INTEGER REFERENCES data2,
data3 INTEGER REFERENCES data3
);

When I delete a record of the table realtions I also want to delete the
record on the data-tables if there is no other record referencing them.
How can I do this?

Regards

Andreas Fromm
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Richard Huxton]

On Tuesday 26 August 2003 15:06, Andreas Fromm wrote:

When I delete a record of the table realtions I also want to delete the
record on the data-tables if there is no other record referencing them.
How can I do this?

You want to add "ON DELETE CASCADE" to the REFERENCES... clause.
See the SQL reference for full syntax details.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Bruno Wolff III]

On Tue, Aug 26, 2003 at 18:34:04 +0100,
Richard Huxton <de*@archonet.com> wrote:

On Tuesday 26 August 2003 15:06, Andreas Fromm wrote:

When I delete a record of the table realtions I also want to delete the
record on the data-tables if there is no other record referencing them.
How can I do this?

You want to add "ON DELETE CASCADE" to the REFERENCES... clause.
See the SQL reference for full syntax details.

I don't think that is what he wants. He seems to want to go in the other
direction. That is going to require custom triggers and probably adding a
reference count column to data-tables.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Bruno Wolff III]

On Wed, Aug 27, 2003 at 00:09:34 +0200,
Andreas Fromm <An***********@physik.uni-erlangen.de> wrote:

Bruno Wolff III wrote:

On Tue, Aug 26, 2003 at 18:34:04 +0100,
Richard Huxton <de*@archonet.com> wrote:
I don't think that is what he wants. He seems to want to go in the other
direction. That is going to require custom triggers and probably adding a
reference count column to data-tables.

This is what I want to do, but my question is how can I easely now if
there is someone referencing a certain record. Is it just possible by
searching every table that could reference it or is there an other way
of doing it without adding an reference count column?

You can use a reference count in the row you are interested in. You
would need to create triggers on all referncing tables to update the
reference count as necessary. Also as pointed out previously, you
want to use serialized isolation (and handle retries) or exclusively lock
the table (and worry about deadlocks and a concurrency bottleneck)
if you use this approach.

You could do a scan of all possible referencing tables whenever you update
any of the referencing tables. However, this approach has pretty much the
same problems as using a refernce count and will probably be slower.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend