deleting referenced data
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
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
On Tue, Aug 26, 2003 at 18:34:04 +0100,
Richard Huxton <dev@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.
On Tuesday 26 August 2003 18:55, Bruno Wolff III wrote:
On Tue, Aug 26, 2003 at 18:34:04 +0100,
Richard Huxton <dev@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.
Ah - you're quite right Bruno. That's the approach I'd take. Andreas - take
care with simultaneous adding/removal in the relations table - that could
lead to an unexpected failure unless you make sure you're using serializable
transaction level.
--
Richard Huxton
Archonet Ltd
Bruno Wolff III wrote:
On Tue, Aug 26, 2003 at 18:34:04 +0100,
Richard Huxton <dev@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?
Regards
Andreas
On Tuesday 26 August 2003 23:09, Andreas Fromm wrote:
Bruno Wolff III wrote:
On Tue, Aug 26, 2003 at 18:34:04 +0100,
Richard Huxton <dev@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?
Those are basically your two options.
--
Richard Huxton
Archonet Ltd
On Wed, Aug 27, 2003 at 00:09:34 +0200,
Andreas Fromm <Andreas.Fromm@physik.uni-erlangen.de> wrote:
Bruno Wolff III wrote:
On Tue, Aug 26, 2003 at 18:34:04 +0100,
Richard Huxton <dev@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.
Bruno Wolff III wrote:
On Wed, Aug 27, 2003 at 00:09:34 +0200,
Andreas Fromm <Andreas.Fromm@physik.uni-erlangen.de> wrote:Bruno Wolff III wrote:
On Tue, Aug 26, 2003 at 18:34:04 +0100,
Richard Huxton <dev@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.
As the newbe I am, I don't understand much abaut serialized isolation,
db-locks and so on.
Do I need to care abaut all these considerng that the database will only
be accessed by one user at a time, and the hole db will not have many
entries?
Are there basic dessign considerations that should be taken, or is it
something that can be added in the future?
As I don't have much time to invest in this project, I will have to come
up with some other solution. Maybe something like a 'garbage collection'
routine, that runs over the db from time to time and delets all
unreferenced records.
--
Andreas Fromm
PLEASE DONT USE THIS ADVICE WITHOUT TESTING IT:
You might be able to do it by a) setting up foreign key constraints,
setting the record to "on delete restrict", and then simply setting up a
trigger that attempts to delete it after every record delete. This may
screw up your transaction though, or not work at all.
Jon
On Wed, 27 Aug 2003, Andreas Fromm wrote:
Show quoted text
Bruno Wolff III wrote:
On Tue, Aug 26, 2003 at 18:34:04 +0100,
Richard Huxton <dev@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?Regards
Andreas
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match