pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDeleting entries from multiple tables
5+ messages / 3 participants
[nested] [flat]
* Deleting entries from multiple tables
@ 2010-11-30 05:59 manidegr8 <engineer.usman@ymail.com>
2010-11-30 08:15 ` Re: Deleting entries from multiple tables Dusan Misic <promisic@gmail.com>
2010-11-30 08:19 ` Re: Deleting entries from multiple tables Dusan Misic <promisic@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: manidegr8 @ 2010-11-30 05:59 UTC (permalink / raw)
To: pgsql-sql
i am trying to run a query but its not working may be due to constraint
conflicts
i have table A, B and C
B and C have a foreign key linked with A's primary key
so i want to delete an entry from A
for that i hav to delete child records first
can u design a query which wont conflict the constraints..?
Thanks...
Regards.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Deleting-entries-from-multiple-tables-tp3285654p3285654.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Deleting entries from multiple tables
2010-11-30 05:59 Deleting entries from multiple tables manidegr8 <engineer.usman@ymail.com>
@ 2010-11-30 08:15 ` Dusan Misic <promisic@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Dusan Misic @ 2010-11-30 08:15 UTC (permalink / raw)
To: manidegr8 <engineer.usman@ymail.com>; +Cc: pgsql-sql
What is ON DELETE part of the foreign key constraint?
If you set it to CASCADE, then your delete from the parent table will
cascade to child tables (to put it simpler, when you delete record in table
A, then PostgreSQL will delete any rows in tables B and C that are
referencing original row (or column) in table A).
If you used defaults when you created your FOREIGN KEY constraint, the
default is to restrict ANY deletion in table A if it is being referenced in
table B or C.
On Tue, Nov 30, 2010 at 6:59 AM, manidegr8 <engineer.usman@ymail.com> wrote:
>
> i am trying to run a query but its not working may be due to constraint
> conflicts
>
> i have table A, B and C
> B and C have a foreign key linked with A's primary key
>
> so i want to delete an entry from A
> for that i hav to delete child records first
>
> can u design a query which wont conflict the constraints..?
>
> Thanks...
> Regards.
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/Deleting-entries-from-multiple-tables-tp3285654p3285654.html
> Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Deleting entries from multiple tables
2010-11-30 05:59 Deleting entries from multiple tables manidegr8 <engineer.usman@ymail.com>
@ 2010-11-30 08:19 ` Dusan Misic <promisic@gmail.com>
2010-11-30 08:36 ` Re: Deleting entries from multiple tables Achilleas Mantzios <achill@matrix.gatewaynet.com>
2010-11-30 10:00 ` Re: Deleting entries from multiple tables manidegr8 <engineer.usman@ymail.com>
1 sibling, 2 replies; 5+ messages in thread
From: Dusan Misic @ 2010-11-30 08:19 UTC (permalink / raw)
To: manidegr8 <engineer.usman@ymail.com>; +Cc: pgsql-sql
To be precise, if you used defaults, you can't delete any row in table A
that has rows referencing to it in tables B and C.
On Tue, Nov 30, 2010 at 6:59 AM, manidegr8 <engineer.usman@ymail.com> wrote:
>
> i am trying to run a query but its not working may be due to constraint
> conflicts
>
> i have table A, B and C
> B and C have a foreign key linked with A's primary key
>
> so i want to delete an entry from A
> for that i hav to delete child records first
>
> can u design a query which wont conflict the constraints..?
>
> Thanks...
> Regards.
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/Deleting-entries-from-multiple-tables-tp3285654p3285654.html
> Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Deleting entries from multiple tables
2010-11-30 05:59 Deleting entries from multiple tables manidegr8 <engineer.usman@ymail.com>
2010-11-30 08:19 ` Re: Deleting entries from multiple tables Dusan Misic <promisic@gmail.com>
@ 2010-11-30 08:36 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
1 sibling, 0 replies; 5+ messages in thread
From: Achilleas Mantzios @ 2010-11-30 08:36 UTC (permalink / raw)
To: pgsql-sql
Here is my take on how to regard parent tables in one-to-many FK relationships.
If lets say we have a situation when we model e.g. mail messages and its
attachments, then we might want to use ON DELETE CASCADE since
there is absolutely no reason for an attachment to exist when the main message is gone.
But, when we model e.g. machinery in a car and we have an attribute : maker of parts modeled
as a parent table and the child parts table pointing to this maker table,
then this parent table is not of high significance and we should not put ON DELETE CASCADE.
In this way, we protect the database of deleting vital data.
Note that under the parts table, other tables might point to, which would include history,
plan maintenace, and other very vital data.
So, you are enouraged to put ON DELETE CASCADE ONLY when you are confident that the child table's rows
have absolutely no meaning without the parent table row.
Otherwise, leave the default behaviour.
Στις Tuesday 30 November 2010 10:19:04 ο/η Dusan Misic έγραψε:
> To be precise, if you used defaults, you can't delete any row in table A
> that has rows referencing to it in tables B and C.
>
> On Tue, Nov 30, 2010 at 6:59 AM, manidegr8 <engineer.usman@ymail.com> wrote:
>
> >
> > i am trying to run a query but its not working may be due to constraint
> > conflicts
> >
> > i have table A, B and C
> > B and C have a foreign key linked with A's primary key
> >
> > so i want to delete an entry from A
> > for that i hav to delete child records first
> >
> > can u design a query which wont conflict the constraints..?
> >
> > Thanks...
> > Regards.
> > --
> > View this message in context:
> > http://postgresql.1045698.n5.nabble.com/Deleting-entries-from-multiple-tables-tp3285654p3285654.html
> > Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
> >
> > --
> > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-sql
> >
>
--
Achilleas Mantzios
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Deleting entries from multiple tables
2010-11-30 05:59 Deleting entries from multiple tables manidegr8 <engineer.usman@ymail.com>
2010-11-30 08:19 ` Re: Deleting entries from multiple tables Dusan Misic <promisic@gmail.com>
@ 2010-11-30 10:00 ` manidegr8 <engineer.usman@ymail.com>
1 sibling, 0 replies; 5+ messages in thread
From: manidegr8 @ 2010-11-30 10:00 UTC (permalink / raw)
To: pgsql-sql
yes i know about on delete cascade
it automatically delete the child when master is deleted
but the database i m working on is not built in this fashion
tables r built on default rules
so constraints r there
i m jus asking to build a procedure or function
that taking from the user and deletes the child and master records against
that input...
i hav tried it on pgadmin 111
but its not working...
please guide me
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Deleting-entries-from-multiple-tables-tp3285654p3285832.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2010-11-30 10:00 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-11-30 05:59 Deleting entries from multiple tables manidegr8 <engineer.usman@ymail.com>
2010-11-30 08:15 ` Dusan Misic <promisic@gmail.com>
2010-11-30 08:19 ` Dusan Misic <promisic@gmail.com>
2010-11-30 08:36 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2010-11-30 10:00 ` manidegr8 <engineer.usman@ymail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox