Lennart wrote:
> On Jun 27, 10:31 am, Lennart <Erik.Lennart.Jons...@[EMAIL PROTECTED]
> wrote:
> [...]
> > The other day I stumbled upon an error. The foreign keys
> > are working as expected, but syscat.keycoluse.colseq indicates
> > otherwise(I'm aware that syscat.references can be used instead, this
> > is more of a nice to know thing)
>
> Hmm, on second thought. Looking in:
>
> SQL Reference Volume 1, Version 8. (havent checked 9.5 yet) in the
> comment on syscat.references it says:
>
> FK_COLNAMES:"List of foreign key column names. Warning: This column
> will be removed in the future.Use SYSCAT.KEYCOLUSE for this
> information."
>
> PK_COLNAMES: "List of parent key column names. Warning: This column
> will be removed in the future. Use SYSCAT.KEYCOLUSE for this
> information."
>
> But as shown in my earlier post, KEYCOLUSE.COLSEQ does not necessarily
> reflect the colorder in the key. Reflections anyone?
Indeed, FK_COLNAMES and PK_COLNAMES are still there in 9.5, but they've
been deprecated for a while now. I tested out the DDL you posted on our
9.5 FP1 box and came up with the exact same results (incorrect ordering
in KEYCOLUSE).
Insertions and deletions to both tables worked fine, so the keys are
defined properly - it's just that SYSCAT.KEYCOLUSE isn't re****ting them
correctly. The deprecated FK_COLNAMES and PK_COLNAMES columns are
re****ting them in the correct order.
I also tested the following query, which is a tweaked version of a
query I use in my do***entation system for obtaining the mapping of
foreign key columns to primary key columns:
SELECT
SUBSTR(R.CONSTNAME, 1, 18) AS KEYNAME,
SUBSTR(KF.COLNAME, 1, 18) AS COLNAME,
SUBSTR(KP.COLNAME, 1, 18) AS REFCOLNAME
FROM
SYSCAT.REFERENCES R
INNER JOIN SYSCAT.KEYCOLUSE KF
ON R.TABSCHEMA = KF.TABSCHEMA
AND R.TABNAME = KF.TABNAME
AND R.CONSTNAME = KF.CONSTNAME
INNER JOIN SYSCAT.KEYCOLUSE KP
ON R.REFTABSCHEMA = KP.TABSCHEMA
AND R.REFTABNAME = KP.TABNAME
AND R.REFKEYNAME = KP.CONSTNAME
WHERE
R.TABNAME = 'COURSE_OFFERING_ADV_PRG_ELIG'
AND KF.COLSEQ = KP.COLSEQ
ORDER BY
R.TABSCHEMA,
R.TABNAME,
R.CONSTNAME,
KF.COLSEQ
WITH UR;
Naturally, the results were:
KEYNAME COLNAME REFCOLNAME
------------------ ------------------ ------------------
XFK1CO_ADV_PRG_ELG COURSEOFFERING_ID ADMISSIONROUND_ID
XFK1CO_ADV_PRG_ELG EDUCATIONORG_ID EDUCATIONORG_ID
XFK1CO_ADV_PRG_ELG ADMISSIONROUND_ID COURSEOFFERING_ID
Oh dear. I'd suggest opening a PMR for this - looks like a bug to me.
Cheers,
Dave.
|
