Problem with constraints on SE
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 ? TIA Dirk
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,
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.
Art S. Kagel
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
Add on...
there is an foreign key from fremdapl.fap01 to aplatz.apl001.
{ TABLE "besdo".aplatz row size = 509 number of columns = 101 index size = 24 }
create table "besdo".aplatz
(
apl001 integer,
apl002 integer,
apl003 char(3),
apl004 char(60),
...
apl017 integer,
apl018 integer,
apl020 integer,
...
apl114 integer,
apl115 integer,
unique (apl001) constraint "besdo".u134_6
);
revoke all on "besdo".aplatz from "public";
create index "besdo".ix172_17 on "besdo".aplatz (apl018);
May be the problem occurs, because there isn't an primary key, only
an unique index on the referenced column ???
TIA
Dirk