473,216 Members | 1,245 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,216 software developers and data experts.

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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 11 '05 #1
3 1769
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

20
by: Hanzo | last post by:
I'm iterating over a vector of base class pointers and deleting those which meet a certain criteria...i'm using pretty text-book code for the particular delete/erasure (it's straight out of Myers'...
1
by: dev | last post by:
Hej There. We have a big problem. We have now for 4th years had a SQL Server without problems. But sutnely some of the primary keys are deleted. The subdata to the primary keys are not deleted....
5
by: Alex | last post by:
Hi, I have two tables , A and B where table B has a foreign key constraint to table A. I want to delete all records in table A that are older than a certain date that are not referenced by...
0
by: Bandit | last post by:
I'm populating a gridview (called docList) with document info from a network folder like so: Sub Show_Files(ByVal inputDir As String) Dim strFileNamePath As String = inputDir Dim dirInfo As...
6
by: Martin Bischoff | last post by:
Hi, I'm creating temporary directories in my web app (e.g. ~/data/temp/temp123) to allow users to upload files. When I later delete these directories (from the code behind), the application...
9
by: Hamed | last post by:
Hello I have a DataGrid that a is bound to a DataTable. Some of the rows in the DataTable should not be deleted. How can I prohibit deleting of some identified rows? The problem could be...
7
by: Susan Mackay | last post by:
I have a data table that is connected to a database table with a data adapter in the 'standard' manner. However I want to be able to remove selected rows from the data table (i.e. no longer...
4
by: sphinney | last post by:
I'm not exactly sure how to start this post. My question is pretty simple, but it will take a little bit of context before I can state it. (And thanks in advance for taking the time to read this!) ...
1
by: Kyosuke18 | last post by:
Hi everyone, I have a problem in deleting a data that is connected on the database.. I tried this code but it shows me an error: Run-time error '-2147217900(80040e14)': Syntax error in string in...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.