By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,792 Members | 1,488 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,792 IT Pros & Developers. It's quick & easy.

deleting referenced data

P: n/a
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

Nov 11 '05 #1
Share this Question
Share on Google+
3 Replies

P: n/a
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

Nov 11 '05 #2

P: n/a
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

Nov 11 '05 #3

P: n/a
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

Nov 11 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.