Thread: Copy/foreign key contraints
Being fairly new to postgres I'm trying to find my way around this, did some searching and see this has come up quite a few times, but.......... table 1: customer.custid primary key table 2: transx.custid foreign key When using COPY to import data I received; "<unnamed>referential integerity violation-key referenced from table transx not found in customer" (fairly vague statement) I'm sure many know where this is going, but I'll still explain what I've tried so far....... Then created a copy/temp table of transx with no constraints and COPY'ed data into it, ran some "where not exists" statements against the customer.custid and found no unmatched primary/foreign keys. Tried to do an INSERT from the temp table into the transx table and still got the referential integerity violation. At this point I'm presuming that my data's ok, so now what..... Drop the foreign key, import data, then add foreign key back? But what if there is a real "referential integerity violation"? How are others dealing with this or am I just doing something wrong? postgres 7.2.1 / Rh7.3 Also as a side note; It would be nice while data import if it would just create an exception file and continue the import of data with a way to see/count exceptions at the end of import with a choice to then process the file and save the exceptions. Sorry to ramble........ Ken
explore options and output of pgdump, it must do it correctly Ken Godee wrote: > Being fairly new to postgres I'm trying to find my way > around this, did some searching and see this has come > up quite a few times, but.......... > > table 1: customer.custid primary key > table 2: transx.custid foreign key > > When using COPY to import data I received; > > "<unnamed>referential integerity violation-key referenced from > table transx not found in customer" (fairly vague statement) > > I'm sure many know where this is going, but I'll still explain > what I've tried so far....... > > Then created a copy/temp table of transx with no constraints > and COPY'ed data into it, ran some "where not exists" statements > against the customer.custid and found no unmatched > primary/foreign keys. Tried to do an INSERT from the temp table > into the transx table and still got the referential integerity violation. > At this point I'm presuming that my data's ok, so now what..... > > Drop the foreign key, import data, then add foreign key back? > But what if there is a real "referential integerity violation"? > How are others dealing with this or am I just doing something > wrong? > > postgres 7.2.1 / Rh7.3 > > Also as a side note; > It would be nice while data import if it > would just create an exception file and continue the import > of data with a way to see/count exceptions at the end of import > with a choice to then process the file and save the exceptions. > > Sorry to ramble........ > > Ken > > > > > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
The data's not originally coming from a postgres database, it's in a ascii delimited format, sorry for not mentioning that. > explore options and output of pgdump, it must do it correctly > > Ken Godee wrote: > > Being fairly new to postgres I'm trying to find my way > > around this, did some searching and see this has come > > up quite a few times, but.......... > > > > table 1: customer.custid primary key > > table 2: transx.custid foreign key > > > > When using COPY to import data I received; > > > > "<unnamed>referential integerity violation-key referenced from > > table transx not found in customer" (fairly vague statement) > >
On Fri, 13 Dec 2002, Ken Godee wrote: > Being fairly new to postgres I'm trying to find my way > around this, did some searching and see this has come > up quite a few times, but.......... > > table 1: customer.custid primary key > table 2: transx.custid foreign key > > When using COPY to import data I received; > > "<unnamed>referential integerity violation-key referenced from > table transx not found in customer" (fairly vague statement) > > I'm sure many know where this is going, but I'll still explain > what I've tried so far....... > > Then created a copy/temp table of transx with no constraints > and COPY'ed data into it, ran some "where not exists" statements > against the customer.custid and found no unmatched > primary/foreign keys. Tried to do an INSERT from the temp table > into the transx table and still got the referential integerity violation. > At this point I'm presuming that my data's ok, so now what..... It's hard to say what's causing that without more details. Can you crete a simplified self contained example of it misbehaving to send to the list (or failing that if the data/schema isn't secret and not too big, that)
Ron,
The file I'm COPYing is straight ascii data, and just delimited
no sql statements, I'm adding data to an existing table.
ie.
------file-----------
25467^John Doe^480-555-1212^^12/13/2002
---------------------
"COPY transx from '/tmp/transx.dat' delimiters '^' with null as '';
I had thought this was pretty straight forward, I don't
have a very complex set up and thought I was just missing
something (and probally am) or this was a common problem.
Another poster suggested forwarding alittle more info and
schema to list, which I'll post a little later.
> OOPS, correction as:
> Also, if names are NOT double quoted "custID" any reference to them get
> converted to lower case, so you're references must be case-correct too.
> eg CONSTRAINT "mycon" FOREIGN KEY ("custID") REFERENCES .....
>
>
> Ron St.Pierre wrote:
>
> > Check your ascii file and make sure that the column or constraint
> > names are not quoted. If so, check for spaces at the end of names
> > eg CONSTRAINT 'mycon' FOREIGN KEY ('custid ') REFERENCES ...... <-
> > note the space inside quotes after custid
> >
> > Also, if names are double quoted "custID" any reference to them get
> > converted to lower case, so you're references must be case-correct too.
> > eg CONSTRAINT "mycon" FOREIGN KEY ("custID") REFERENCES .....
> >
> >
> >
> > Ken Godee wrote:
> >
> >> The data's not originally coming from a postgres database,
> >> it's in a ascii delimited format, sorry for not mentioning that.
> >>
> >>
> >>> Ken Godee wrote:
> >>>
> >>>
> >>>> table 1: customer.custid primary key
> >>>> table 2: transx.custid foreign key
> >>>>
> >>>> When using COPY to import data I received;
> >>>>
> >>>> "<unnamed>referential integerity violation-key referenced from
> >>>> table transx not found in customer" (fairly vague statement)
> >>>>
> >>>>
> >>>
> >>
> >>
> >> ---------------------------(end of broadcast)---------------------------
> >> TIP 3: if posting/reading through Usenet, please send an appropriate
> >> subscribe-nomail command to majordomo@postgresql.org so that your
> >> message can get through to the mailing list cleanly
> >>
> >>
> >
> >
>
>
> --
> Ron St.Pierre
> Syscor R&D
> tel: 250-361-1681
> email: rstpierre@syscor.com
>
>
>
Thanks for everyones feedback but....... Once again I find myself "Eating crow" Had nothing to do with postgres, my "not exists" statement was wrong and didn't find the missing data. After correctly writting the not exsists statement about 30 rows appeared in the table with the foreign key with no matches from the table with the primary key. > Being fairly new to postgres I'm trying to find my way > around this, did some searching and see this has come > up quite a few times, but.......... > > table 1: customer.custid primary key > table 2: transx.custid foreign key > > When using COPY to import data I received; > > "<unnamed>referential integerity violation-key referenced from > table transx not found in customer" (fairly vague statement) > > I'm sure many know where this is going, but I'll still explain > what I've tried so far....... > > Then created a copy/temp table of transx with no constraints > and COPY'ed data into it, ran some "where not exists" statements > against the customer.custid and found no unmatched > primary/foreign keys. Tried to do an INSERT from the temp table > into the transx table and still got the referential integerity violation. > At this point I'm presuming that my data's ok, so now what..... > > Drop the foreign key, import data, then add foreign key back? > But what if there is a real "referential integerity violation"? > How are others dealing with this or am I just doing something > wrong? > > postgres 7.2.1 / Rh7.3 > > Also as a side note; > It would be nice while data import if it > would just create an exception file and continue the import > of data with a way to see/count exceptions at the end of import > with a choice to then process the file and save the exceptions. > > Sorry to ramble........ > > Ken > > > > > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >