Thread: A question about constraints.

Hullo,

I have added a constraint to a table to prevent it loading
data unless its key field is in another table (whose data
I have previously loaded)


ALTER TABLE OnMap ADD CONSTRAINT objid_check1   FOREIGN KEY (fname, lname)
                                                REFERENCES Object (fname, lname);


Indeed, the constraint works:

COPY OnMap FROM '/data/OnMap.dump' USING DELIMITERS '|'

psql:OnMap:1: ERROR:  objid_check1 referential integrity violation - key referenced
 from onmap not found in object

But... is there anyway to get PostgreSQL to tell me which record or records
are failing to load ?

I am assuming I am doing something stupid here.

Cheers

Steve

On Fri, 14 Dec 2001, Stephen Ingram wrote:
> Indeed, the constraint works:
>
> COPY OnMap FROM '/data/OnMap.dump' USING DELIMITERS '|'
>
> psql:OnMap:1: ERROR:  objid_check1 referential integrity violation - key referenced
>  from onmap not found in object
>
> But... is there anyway to get PostgreSQL to tell me which record or records
> are failing to load ?

I believe the copy command tells you the record that it is having problems
with. In your example above it seems the problem occurred in the very
first record. So far every time I have had problems with copy it reported
the line that it had the problems with.

On Friday 14 December 2001 01:44 pm, Francisco Reyes wrote:
> On Fri, 14 Dec 2001, Stephen Ingram wrote:
> > Indeed, the constraint works:
> >
> > COPY OnMap FROM '/data/OnMap.dump' USING DELIMITERS '|'
> >
> > psql:OnMap:1: ERROR:  objid_check1 referential integrity violation - key
> > referenced from onmap not found in object
> >
> > But... is there anyway to get PostgreSQL to tell me which record or
> > records are failing to load ?
>
> I believe the copy command tells you the record that it is having problems
> with. In your example above it seems the problem occurred in the very
> first record. So far every time I have had problems with copy it reported
> the line that it had the problems with.

Hi Francisco,

Thanks for the reply. I'll check that out and see if its the case.

I don't know why I didn't think of that!?   Well, I did think I
was doing something stupid :)

Thanks again.

Steve