RE: Problem with constraints on SE
This sounds like a problem with dbschema. Get Art's myschema from utils2_ak in the IIUG software repository.
The problem is not with fremdapl but with the next table (by tabid). Run dbschema on individual tables to confirm.
Alternatively you can unload and recreate the offending table, but you will need to create the schema manually.
Murray Wood
-----Original Message-----
From: Dirk Niemeier [SMTP:dirk.niemeier@stueken.de]
Sent: Thursday, August 12, 1999 9:50 PM
To: informix-list@iiug.org
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