Re: Problem with constraints on SE

Dirk Sorry for jumping in at this stage and I suspect I dont remember earlier postings on the subject either, but for the error "duplicate value for record with unique key", there is a simple way you can find the duplicate record. The sql for your case follows. Please ignore this if you already know this. select fap01,fap02,fap03, count(*) from fremdapl group by 1,2,3 having count(*) > 1 will give you (fap01,fap02,fap03) combinations which are non-unique. I have had this on IDS 7.1x or 7.2x (dont remember exactly) but could not trace the reasons for this other than that the index may be corrupted (but oncheck could not find it) and the application sent some bad data which was not trapped by the unique index. What I did in was to generate a list of duplicate records to figure out which one was bad and deleted the bad record with its rowid. HTH Sujit Dirk Niemeier <dirk.niemeier@stueken.de> on 08/12/99 02:49:47 AM Please respond to Dirk Niemeier <dirk.niemeier@stueken.de> To: informix-list@iiug.org cc: (bcc: Sujit Pal) Subject: Re: Problem with constraints on SE Hi Art, "Art S. Kagel" schrieb: > Dirk Niemeier wrote: > > > > Hi, > > I get an "ISAM-error : duplicate value for a record with unique key" in > > dbacces at table-info-cOnstraints-Reference -Reference and/or > > referenceD. > > I think there is an problem with on of the system-tables. > > How can I solve it ? > > I'm not sure, but I think it happends after drop/create of an table with > > > > foreign key !!??. > > Is there an other way to show all constraints ? Perhaps an nice > > select-statement ? > > Constraints are in sysconstraints and the referenced tableid for a foreign > key constraint is named in sysreferences.ptabid. The relevant SQL, > extracted from myschema.ec is: > > SELECT st.tabname, rt.tabname, sr.primary, sr.ptabid, sr.delrule, > sc.constrid, constrname, constrtype, si.idxname, si.tabid, only part1...part8 for SE :-) > > si.part1, si.part......., si.part16 > FROM systables st, sysconstraints sc, sysindexes si, sysreferences sr, > systables rt > WHERE st.tabid = sc.tabid > AND rt.tabid = sr.ptabid > AND sc.constrid = sr.constrid > AND sc.tabid = si.tabid > AND sc.idxname = si.idxname > AND sc.constrtype = 'R' > AND st.tabname = "sometablename" > ORDER BY si.tabid, constrid; > > If you suspect that the drop table did not cleanup completely you may > want to make the joins OUTER joins or you may want to query the tables > separately to determine if all the records there make sense. Your statement worked fine, but doesn't solve my problem. The dbschema utility generates an error too. --- { TABLE "niemeier".fremdapl row size = 39 number of columns = 6 index size = 58 } create table "niemeier".fremdapl ( fap01 integer not null constraint "niemeier".n366_708, fap02 char(3) not null constraint "niemeier".n366_709, fap03 char(20) not null constraint "niemeier".n366_710, fap04 decimal(6,2) not null constraint "niemeier".n366_711, fap05 decimal(6,2) not null constraint "niemeier".n369_716, fap06 integer not null constraint "niemeier".n370_717, primary key (fap01,fap02,fap03) constraint "niemeier".pk_fremdapl ); revoke all on "niemeier".fremdapl from "public"; 100 - ISAM error: duplicate value for a record with unique key. --- stop working at this point I can't find the reason why. We are using SE 7.22 UC 1 on HPUX 10.20 TIA Dirk

Hi Sujit, The problem is not an duplicate value in the user-table. There must be an problem in the systables, because dbaccess and dbschema gets an error while examining the foreign key's. Thanks for answer Dirk (I unloaded the refernced tabledata, droped, created the table, created the indices, load the org-data and it worked :-) but why ??) Sujit.Pal@bankofamerica.com schrieb: > Dirk > > Sorry for jumping in at this stage and I suspect I dont remember earlier > postings on the subject either, but for the error "duplicate value for > record with unique key", there is a simple way you can find the duplicate > record. The sql for your case follows. Please ignore this if you already > know this. > > select fap01,fap02,fap03, count(*) > from fremdapl > group by 1,2,3 > having count(*) > 1> > will give you (fap01,fap02,fap03) combinations which are non-unique. I have > had this on IDS 7.1x or 7.2x (dont remember exactly) but could not trace > the reasons for this other than that the index may be corrupted (but > oncheck could not find it) and the application sent some bad data which was > not trapped by the unique index. What I did in was to generate a list of > duplicate records to figure out which one was bad and deleted the bad > record with its rowid. > > HTH > Sujit > > Dirk Niemeier <dirk.niemeier@stueken.de> on 08/12/99 02:49:47 AM > > Please respond to Dirk Niemeier <dirk.niemeier@stueken.de> > > To: informix-list@iiug.org > cc: (bcc: Sujit Pal) > Subject: Re: Problem with constraints on SE > > Hi Art, > > "Art S. Kagel" schrieb: > > > Dirk Niemeier wrote: > > > > > > Hi, > > > I get an "ISAM-error : duplicate value for a record with unique key" in > > > dbacces at table-info-cOnstraints-Reference -Reference and/or > > > referenceD. > > > I think there is an problem with on of the system-tables. > > > How can I solve it ? > > > I'm not sure, but I think it happends after drop/create of an table > with > > > > > > foreign key !!??. > > > Is there an other way to show all constraints ? Perhaps an nice > > > select-statement ? > > > > Constraints are in sysconstraints and the referenced tableid for a > foreign > > key constraint is named in sysreferences.ptabid. The relevant SQL, > > extracted from myschema.ec is: > > > > SELECT st.tabname, rt.tabname, sr.primary, sr.ptabid, sr.delrule, > > sc.constrid, constrname, constrtype, si.idxname, si.tabid,> > only part1...part8 for SE :-) > > > > > si.part1, si.part......., si.part16 > > FROM systables st, sysconstraints sc, sysindexes si, sysreferences sr, > > systables rt > > WHERE st.tabid = sc.tabid > > AND rt.tabid = sr.ptabid > > AND sc.constrid = sr.constrid > > AND sc.tabid = si.tabid > > AND sc.idxname = si.idxname > > AND sc.constrtype = 'R' > > AND st.tabname = "sometablename" > > ORDER BY si.tabid, constrid; > > > > If you suspect that the drop table did not cleanup completely you may > > want to make the joins OUTER joins or you may want to query the tables > > separately to determine if all the records there make sense. > > Your statement worked fine, but doesn't solve my problem. > The dbschema utility generates an error too. > > --- > { TABLE "niemeier".fremdapl row size = 39 number of columns = 6 index size > = > 58 } > create table "niemeier".fremdapl > ( > fap01 integer not null constraint "niemeier".n366_708, > fap02 char(3) not null constraint "niemeier".n366_709, > fap03 char(20) not null constraint "niemeier".n366_710, > fap04 decimal(6,2) not null constraint "niemeier".n366_711, > fap05 decimal(6,2) not null constraint "niemeier".n369_716, > fap06 integer not null constraint "niemeier".n370_717, > primary key (fap01,fap02,fap03) constraint "niemeier".pk_fremdapl > ); > revoke all on "niemeier".fremdapl from "public";> > 100 - ISAM error: duplicate value for a record with unique key. > --- stop working at this point > > I can't find the reason why. > We are using SE 7.22 UC 1 on HPUX 10.20 > TIA > Dirk