Thread: 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
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: > 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 >
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
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.