Re: Removing records that violate foreign key

I usually use an SELECT with an OUTER JOIN that uses the foreign key with
a USING or ON clause. Then in the where clause I look for any records
where some field from the primary key table that is NOT NULL is null.
Something like:

SELECT oid FROM fktbl LEFT OUTER JOIN pktbl USING(keyname) WHERE
notnullfield IS NULL ;

You can then use it as a sub-select in a DELETE command or pump the result
out to a file so you can see if you can tell where it all came from. I
used this once to figure out that a trigger I had firing "BEFORE UPDATE"
had a boundary condition in it.

- brian

On Tue, 18 Jun 2002, Cliff Wells wrote:

>
> Hello,
>
> I'm porting an application's data from a proprietary format into PostgreSQL
> 7.2.1. I'm using ecpg and I basically follow this process:
>
> create tables
> create indexes
> import data
> add foreign key constraints
>
> I have to add the foreign key constraint last as otherwise the import will fail
> as the records being referenced won't necessarily exist in PostgreSQL yet. The
> problem is that now I'd like to delete any records that violate the constraint.
> Any suggestions on a quick way to do this?
>
> --
> Cliff Wells, Software Engineer
> Logiplex Corporation (www.logiplex.net)
> (503) 978-6726 x308 (800) 735-0555 x308
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

Wm. Brian McCane | Life is full of doors that won't open
Search http://recall.maxbaud.net/ | when you knock, equally spaced amid those
Usenet http://freenews.maxbaud.net/ | that open when you don't want them to.
Auction http://www.sellit-here.com/ | - Roger Zelazny "Blood of Amber"