Comment: DomainKeys? See http://antispam.yahoo.com/domainkeys
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=private;
 d=mobilefundas.com;
	b=Q7kYcrnG8n3VRFFwjewWHzVb64SFsM/myGSEAGaG9YoAtpYy7Rb9N34DbJC+LyJN;
/m: 45
Received: from unknown (HELO ?121.247.164.219?)
	(dipesh.mistry@mobilefundas.com@121.247.164.219)
	by mail.mobilefundas.com with ESMTPA; 23 Feb 2008 07:10:34 -0000
Message-ID: <47BFC74D.9090200@mobilefundas.com>
Date: Sat, 23 Feb 2008 12:42:13 +0530
From: dipesh <dipesh.mistry@mobilefundas.com>
User-Agent: Thunderbird 1.5.0.12 (X11/20071020)
MIME-Version: 1.0
To: felix@crowfix.com
CC: pgsql-sql@postgresql.org
Subject: Re: Delete with foreign keys
References: <20080222180021.GA7700@crowfix.com>
In-Reply-To: <20080222180021.GA7700@crowfix.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.1 tagged_above=0 required=5 tests=RDNS_DYNAMIC=0.1
X-Spam-Level: 
X-Archive-Number: 200802/173
X-Sequence-Number: 30264

felix@crowfix.com wrote:
> I have three tables --
>
>     CREATE TABLE name (id INT PRIMARY KEY, str VARCHAR(20));
>     CREATE TABLE place (id INT PRIMARY KEY, name_id INT REFERENCES name(id));
>     CREATE TABLE data (id INT PRIMARY KEY, place_id INT REFERENCES place(id));
>
> I want to delete all place and data rows which reference specific
> names, but not the names themselves.  I can do it like this:
>
>     DELETE FROM data d WHERE exists (SELECT id from place p WHERE d.place_id = p.id AND p.name_id IN (SELECT id FROM name WHERE str LIKE 'Fredonia%'));
>
>     DELETE FROM place WHERE name_id IN (SELECT id FROM name WHERE str LIKE 'Fredonia%');
>
> but it seems rather roundabout, and I wonder whether the EXISTS and IN
> business is slow.  Is there some way to do it using JOINs?  I think of
> something like this:
>
>     DELETE FROM place p, name n WHERE p.name_id = n.id AND n.str LIKE 'Fredonia%';
>
> but I don't want to delete the name rows.  Then I think of this:
>
>     DELETE FROM place p WHERE p.name_id = name.id AND name.str LIKE 'Fredonia%';
>
> but I feel uneasy about the two separate name references when the
> table is not named in the FROM clause.  Maybe that's just my novicity.
>
> I also wonder about getting fancy and ending up with SQL specific to a
> database; I don't have any plans to migrate, but I try to avoid
> branding my SQL.
>
>   
Hello, I am Dipesh Mistry from Ahmedabad-India.
I have the solution for you.
First drop the constraint on table data.
Example.
    alter table data drop constraint data_place_id_fkey;
And in second step add new Constraint.
Example.
    alter table data add constraint data_place_id_fkey FOREIGN 
KEY(place_id) REFERENCES place(id) ON DELETE CASCADE ON UPDATE CASCADE;

Now when you test query like,

delete from place where name_id in(select id from name where str='dip%');

So, you can try this above step.

-- 
With Warm Regards,
Dipesh Mistry
Information Technology Dept.
GaneshaSpeaks.com