Bruce,
Does this help? Change YOURPKTABLENAME to, uhm, your PK table name.
SELECT fk2.sys_index_id=5 as `FK ID`, +
fk2.sys_index_name=18 as `FK Name`, +
ft3.sys_table_name=18 as `FK Table`, +
fk2.sys_column_name=18as `FK Column` +
FROM sys_indexes pk1, sys_indexes fk2, sys_tables ft3 +
WHERE +
(pk1.sys_table_id = +
(SELECT sys_table_id +
FROM sys_tables where sys_table_name = 'YOURPKTABLENAME')) +
AND (pk1.sys_primary_key = 1) +
AND (fk2.sys_foreign_key = pk1.sys_index_id) +
AND (fk2.sys_table_id = ft3.sys_table_id) +
ORDER BY fk2.sys_index_id, fk2.sys_column_name
If not, are there any remaining non-zero sys_foreign_key rows in
sys_indexes?
Bill
On Thu, Oct 7, 2010 at 1:55 PM, Bruce Chitiea <[email protected]> wrote:
> All:
>
>
>
> A fair number of FK and NULL messages still refer to the original table
> names, while key definitions reference the new table and column names.
>
> So I delete all FKs, toggle NOT NULL switches to effect a clean sweep.
>
> Except. One PK in one table whose message references the old table name
> refuses deletion: "Cannot Delete a Referenced Primary or Unique Key!". There
> was only one FK reference to this PK in the model, and this was deleted with
> the rest. All -repeat- all FKs in my small schema have been deleted.
>
> LIST CONSTRAINT continues to display this PK as referenced, but displays no
> related FK.
>
> 'ALTER TABLE <tablename> DROP CONSTRAINT <name>' produces: "-ERROR- This
> key is referenced by a Foreign Key - unable to drop constraint. (2719).
>
> Thoughts?
>
> bruce chitiea
> safesectors inc.
>
>
> > -------- Original Message --------
> > Subject: [RBASE-L] - RE: AutoChk / Index Error
> > From: "Bruce Chitiea" <[email protected]>
> > Date: Thu, October 07, 2010 9:57 am
> > To: [email protected] (RBASE-L Mailing List)
> >
> >
> > Bill:
> >
> > Thanks, excellent lead. The default messages for the FKs are still
> > there, but still reference the ORIGINAL referenced table names. I'll do
> > a key sweep and report back.
> >
> > bruce chitiea
> > safesectors inc.
> >
> > > -------- Original Message --------
> > > Subject: [RBASE-L] - RE: AutoChk / Index Error
> > > From: Bill Downall <[email protected]>
> > > Date: Thu, October 07, 2010 9:43 am
> > > To: [email protected] (RBASE-L Mailing List)
> > >
> > >
> > > Bruce,
> > >
> > > I recently encountered this myself. When I did a LIST tablename for the
> > > table where the error happened, the constraints looked correct. But
> when I
> > > went into Database Explorer table designer to look at the keys, one or
> more
> > > of the FK's had lost its default messages. I dropped those constraints,
> > > checked that the data would allow the FK constraint, and recreated the
> FK.
> > > The autochk error went away.
> > >
> > > I have also seen an occasional error where the LIST CONSTRAINTS results
> > > would not show any table in the "referenced" column for a foreign key.
> I
> > > don't think Autochk gave the Error reading index list message then, but
> > > instead gave a message like 'referenced table not valid."
> > >
> > > Bill
> > >
> > > On Thu, Oct 7, 2010 at 12:14 PM, Bruce Chitiea <[email protected]
> >wrote:
> > >
> > > > Buddy;
> > > >
> > > > 'No indexes' is the expected result of LIST INDEX as I'm relying on
> the
> > > > implicit indexing of the PKs and FKs for now.
> > > >
> > > > LIST CONSTRAINT (thanks for the tip) shows all the PKs, FKs, PK
> > > > REFERENCEDs and NOT NULLs I expect to see.
> > > >
> > > > The "Error Reading the index list" result of AUTOCHK is the puzzler.
> > > >
> > > > I have R:Scope at another office. Is that my next stop?
> > > >
> > > > bruce chitiea
> > > > safesectors inc.
> > > > v8.0.23.30809 SU
> > > >
> > > > > -------- Original Message --------
> > > > > Subject: [RBASE-L] - RE: AutoChk / Index Error
> > > > > From: "Walker, Buddy" <[email protected]>
> > > > > Date: Thu, October 07, 2010 8:45 am
> > > > > To: [email protected] (RBASE-L Mailing List)
> > > > >
> > > > >
> > > > > Bruce
> > > > >
> > > > > Maybe I missed something but I don't see where you are creating an
> > > > index. That is why when you "List INDEX" nothing is found. If you
> want to
> > > > see your PK and FK do "LIST CONSTRAINT"
> > > > >
> > > >
> > > >
> > > >
>
