Message-ID: <016a01bfa53a$31d82bc0$e2311a26@bigpanda.com>
From: "Stephan Szabo" <sszabo@bigpanda.com>
To: "Arindam Guha" <arindam_guha@hotmail.com>
Cc: <pgsql-sql@postgresql.org>
References: <20000412230351.60719.qmail@hotmail.com>
Subject: Re: foreign key
Date: Thu, 13 Apr 2000 07:18:36 -0400
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

How did you define the foreign key constraint on the tables?
Also, are you sure that on update of child table it is saying
that the parent record exists instead of doesn't (see below)?

I would guess from the behavior you are seeing that you 
did not define any actions for update or delete.  In that case,
updates to the master will fail if any child references that row,
and updates to the child will fail if the master you are updating
to does not exist.  

I'd say that you probably want to define the cascade action
for update, so that changes to the master table will propogate 
into the child table.  To do this, you need to say "on update cascade"
in the definition of the constraint (for example:
 masterid int references master(id) on update cascade
)

And right now, the only way to permanently delete a foreign key 
constraint is to remove the triggers.  You need to find the
rows in pg_trigger that are associated with the constraint and
drop those triggers.  There should be 3 triggers I believe.

----- Original Message ----- 
From: Arindam Guha <arindam_guha@hotmail.com>
To: <pgsql-sql@postgresql.org>
Sent: Wednesday, April 12, 2000 7:03 PM
Subject: [SQL] foreign key


> How to avoid  updation anomaly like this :
> 
> While trying to update in the parent table it says child record exists
> and
> while trying to update in the child table it says parent record exists
> 
> I am trying to update the foreign key column in both the tables ?
> 
> Also how to delete a foreign key constraint ?
> 
> Thanks
> Arindam
> ______________________________________________________
> Get Your Private, Free Email at http://www.hotmail.com
>