From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
Organization: Dynacom Tankers Mgmt
To: pgsql-sql@postgresql.org
Subject: Re: Deleting entries from multiple tables
Date: Tue, 30 Nov 2010 11:36:29 +0300
User-Agent: KMail/1.9.7
References: <1291096741884-3285654.post@n5.nabble.com>
	<AANLkTikKy2cCU8st0p=Dw=JynL6VBF-vJsdYL4hsZqKX@mail.gmail.com>
In-Reply-To: <AANLkTikKy2cCU8st0p=Dw=JynL6VBF-vJsdYL4hsZqKX@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <201011301036.30047.achill@matrix.gatewaynet.com>

Here is my take on how to regard parent tables in one-to-many FK relationsh=
ips.
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=20
there is absolutely no reason for an attachment to exist when the main mess=
age is gone.
But, when we model e.g. machinery in a car and we have an attribute : maker=
 of parts modeled=20
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 i=
nclude 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.

=CE=A3=CF=84=CE=B9=CF=82 Tuesday 30 November 2010 10:19:04 =CE=BF/=CE=B7 Du=
san Misic =CE=AD=CE=B3=CF=81=CE=B1=CF=88=CE=B5:
> 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.
>=20
> On Tue, Nov 30, 2010 at 6:59 AM, manidegr8 <engineer.usman@ymail.com> wro=
te:
>=20
> >
> > 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
> >
>=20


=2D-=20
Achilleas Mantzios