The KEYCOLUSE.COLSEQ column not showing the correct column
sequence in the foreign key if an existing unique index is used
for the primary key (SQL0598W) and the ordering of the columns
between the index and the primary key don't match.
For example consider the following-
create db test ;
connect to test ;
CREATE TABLE SCHEMA.A (
EDUCATIONORG_ID CHAR(3) NOT NULL ,
COURSEOFFERING_ID CHAR(5) NOT NULL ,
ADMISSIONROUND_ID CHAR(10) NOT NULL
) IN USERSPACE1 ;
CREATE UNIQUE INDEX SCHEMA.UNIINDEX ON SCHEMA.A
(COURSEOFFERING_ID ASC, EDUCATIONORG_ID ASC, ADMISSIONROUND_ID
ASC) ALLOW REVERSE SCANS;
ALTER TABLE SCHEMA.A ADD CONSTRAINT PRIMKEY PRIMARY KEY (
ADMISSIONROUND_ID, EDUCATIONORG_ID, COURSEOFFERING_ID);
CREATE TABLE SCHEMA.B (
ADMISSIONROUND_ID CHAR(10) NOT NULL,
EDUCATIONORG_ID CHAR(3) NOT NULL,
COURSEOFFERING_ID CHAR(5) NOT NULL,
) IN USERSPACE1 ;
ALTER TABLE SCHEMA.B ADD CONSTRAINT FRKEY FOREIGN KEY
(ADMISSIONROUND_ID, EDUCATIONORG_ID,COURSEOFFERING_ID)
REFERENCES SCHEMA.A ON DELETE CASCADE ON UPDATE RESTRICT ;
................................................................
.....................................
Note-For table A the Primary key PRIMKEY internally uses unique
index UNIINDEX(SQL0598W) as the columns in the
unique index and priamry key are the same,though the order of
the column is different.
Now run the following select statement to retrieve the column
sequence in the primary and foreign key.
SELECT substr(K.TABNAME,1,25) tabname, substr(K.COLNAME,1,25)
colname, substr(K.CONSTNAME,1,18) constname, K.COLSEQ FROM
SYSCAT.KEYCOLUSE K WHERE K.TABSCHEMA = 'SCHEMA' AND K.TABNAME IN
('B','A') ;
This should ideally show the following order .Which is the case
for V8 .
................................................................
.....................
TABNAME COLNAME CONSTNAME
COLSEQ
------------------------- -------------------------
------------------ ------
A ADMISSIONROUND_ID PRIMKEY 1
A EDUCATIONORG_ID PRIMKEY 2
A COURSEOFFERING_ID PRIMKEY 3
B ADMISSIONROUND_ID FRKEY 1
B EDUCATIONORG_ID FRKEY 2
B COURSEOFFERING_ID FRKEY 3
6 record(s) selected.
................................................................
........................
But in V9.X we get the following.
................................................................
........................
TABNAME COLNAME CONSTNAME
COLSEQ
------------------------- -------------------------
------------------ ------
A ADMISSIONROUND_ID PRIMKEY 1
A EDUCATIONORG_ID PRIMKEY 2
A COURSEOFFERING_ID PRIMKEY 3
B COURSEOFFERING_ID FRKEY 1
B EDUCATIONORG_ID FRKEY 2
B ADMISSIONROUND_ID FRKEY 3
6 record(s) selected.
................................................................
.............................
Here the column sequence in the foreign key is shown to be
COURSEOFFERING_ID(1),EDUCATIONORG_ID(2),ADMISSIONROUND_ID(3),
while the actual sequence as per the
DDL statement is
ADMISSIONROUND_ID(1),EDUCATIONORG_ID(2),COURSEOFFERING_ID(3) .
This APAR fixes this anomaly and displays the column sequence
properly.
It is importnat to note that this anomay happens only if both
the following conditions are satisfied.
1)There is a pre existing unique index on the parent table with
same columns as in the priamry key
2)The order of the column is not same as that in the primary
key.
If there is no preexisting unique index on the parent table we
won't see this behaviour.
Also this issue doesn't happen in V8. |
