Postgres Pro
Downloads
Home   >   mailing lists

Thread: Removing records that violate foreign key

Removing records that violate foreign key

From
Cliff Wells
Date:
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

Re: Removing records that violate foreign key

From
Stephan Szabo
Date:
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?


For match full maybe something like:
 delete from fk where not exists (
  select * from pk where pk.pkkeycol1=fk.fkkeycol1 [and ...]
 );

For the unspecified match type, I think you need to add
 fk.fkkeycol1 is not null [and ...]
clauses to the top level where.

Re: Removing records that violate foreign key

From
Brian McCane
Date:
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"