Hi,
In my work on Derby-655(getImportedKeys return duplicate rows), I need to make sure that the metadata.properties's getImportedKeys query has been written correctly. There are many system tables that are joined in this query and it will help to understand how the system tables are related to each other. Is there anyone out there who can provide little more information than what we have in the docs on the system tables? The tables referenced by this query in particular are
SYS.SYSTABLES, SYS.SYSSCHEMAS, SYS.SYSCONSTRAINTS, SYS.SYSFOREIGNKEYS, SYS.SYSCONGLOMERATES, SYS.SYSCOLUMNS, SYS.SYSKEYS. The query in reference is as follows
SELECT CAST ('' AS VARCHAR(128)) AS PKTABLE_CAT, \
S.SCHEMANAME AS PKTABLE_SCHEM, \
TABLENAME AS PKTABLE_NAME, \
COLS.COLUMNNAME AS PKCOLUMN_NAME, \
CAST ('' AS VARCHAR(128)) AS FKTABLE_CAT, \
FKTABLE_SCHEM, \
FKTABLE_NAME, \
FKCOLUMN_NAME, \
CAST (CONGLOMS.DESCRIPTOR.getKeyColumnPosition( \
COLS.COLUMNNUMBER) \
AS SMALLINT) AS KEY_SEQ, \
CAST ((CASE WHEN FK_UPDATERULE='S' \
THEN java.sql.DatabaseMetaData::importedKeyRestrict ELSE \
(CASE WHEN FK_UPDATERULE='R' \
THEN java.sql.DatabaseMetaData::importedKeyNoAction ELSE \
java.sql.DatabaseMetaData::importedKeyNoAction END) END) \
AS SMALLINT) AS UPDATE_RULE, \
CAST ((CASE WHEN FK_DELETERULE='S' \
THEN java.sql.DatabaseMetaData::importedKeyRestrict ELSE \
(CASE WHEN FK_DELETERULE='R' \
THEN java.sql.DatabaseMetaData::importedKeyNoAction ELSE \
(CASE WHEN FK_DELETERULE='C' \
THEN java.sql.DatabaseMetaData::importedKeyCascade ELSE \
(CASE WHEN FK_DELETERULE='U' \
THEN java.sql.DatabaseMetaData::importedKeySetNull ELSE \
java.sql.DatabaseMetaData::importedKeyNoAction END) END) END) END) \
AS SMALLINT) AS DELETE_RULE, \
FK_NAME, \
CONSTRAINTNAME AS PK_NAME, \
CAST (java.sql.DatabaseMetaData::importedKeyNotDeferrable \
AS SMALLINT) AS DEFERRABILITY \
FROM --DERBY-PROPERTIES joinOrder=FIXED \n\
(SELECT F2.keyCONSTRAINTID AS FK_ID, \
FKTB_SCHEMA AS FKTABLE_SCHEM, \
FKTB_NAME AS FKTABLE_NAME, \
COLS2.COLUMNNAME AS FKCOLUMN_NAME, \
CONGLOMS2.DESCRIPTOR.getKeyColumnPosition( \
COLS2.COLUMNNUMBER) AS KEY_SEQ, \
C2.CONSTRAINTNAME AS FK_NAME, \
F2.DELETERULE AS FK_UPDATERULE, \
F2.DELETERULE AS FK_DELETERULE \
FROM --DERBY-PROPERTIES joinOrder=FIXED \n\
(SELECT T2.TABLEID AS FKTB_ID, \
S2.SCHEMANAME AS FKTB_SCHEMA, \
T2.TABLENAME AS FKTB_NAME \
FROM \
SYS.SYSTABLES T2 --DERBY-PROPERTIES index = 'SYSTABLES_INDEX1' \n\
, SYS.SYSSCHEMAS S2 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSSCHEMAS_INDEX1' \n\
WHERE \
((1=1) OR ? IS NOT NULL) \
AND S2.SCHEMANAME LIKE ? \
AND T2.TABLENAME LIKE ? \
AND S2.SCHEMAID = T2.SCHEMAID \
) AS FKTB (FKTB_ID, FKTB_SCHEMA, FKTB_NAME), \
SYS.SYSCONSTRAINTS c2 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCONSTRAINTS_INDEX3' \n\
, SYS.SYSFOREIGNKEYS F2 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSFOREIGNKEYS_INDEX1' \n\
, SYS.SYSCONGLOMERATES CONGLOMS2 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCONGLOMERATES_INDEX1' \n\
, SYS.SYSCOLUMNS COLS2 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCOLUMNS_INDEX1' \n\
WHERE \
FKTB.FKTB_ID = C2.TABLEID \
AND F2.CONSTRAINTID = C2.CONSTRAINTID \
AND FKTB.FKTB_ID = COLS2.REFERENCEID \
AND (CASE WHEN CONGLOMS2.DESCRIPTOR IS NOT NULL THEN \
CONGLOMS2.DESCRIPTOR.getKeyColumnPosition( \
COLS2.COLUMNNUMBER) ELSE \
0 END) <> 0 \
AND F2.CONGLOMERATEID = CONGLOMS2.CONGLOMERATEID \
) AS FKINFO(FK_ID, \
FKTABLE_SCHEM, \
FKTABLE_NAME, \
FKCOLUMN_NAME, \
KEY_SEQ, \
FK_NAME, \
FK_UPDATERULE, \
FK_DELETERULE), \
SYS.SYSCONSTRAINTS c --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCONSTRAINTS_INDEX1' \n\
, SYS.SYSTABLES T --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSTABLES_INDEX2' \n\
, SYS.SYSSCHEMAS S --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSSCHEMAS_INDEX2' \n\
, SYS.SYSKEYS K --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSKEYS_INDEX1' \n\
, SYS.SYSCONGLOMERATES CONGLOMS --DERBY-PROPERTIES joinStrategy = NESTEDLOOP, index = 'SYSCONGLOMERATES_INDEX1' \n\
, SYS.SYSCOLUMNS COLS --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCOLUMNS_INDEX1' \n\
WHERE T.TABLEID = C.TABLEID \
AND C.CONSTRAINTID = FKINFO.FK_ID \
AND FKINFO.KEY_SEQ = CONGLOMS.DESCRIPTOR.getKeyColumnPosition( \
COLS.COLUMNNUMBER) \
AND S.SCHEMAID = T.SCHEMAID \
AND K.CONSTRAINTID = C.CONSTRAINTID \
AND (CASE WHEN CONGLOMS.DESCRIPTOR IS NOT NULL THEN \
CONGLOMS.DESCRIPTOR.getKeyColumnPosition(COLS.COLUMNNUMBER) ELSE 0 END) <> 0 \
AND K.CONGLOMERATEID = CONGLOMS.CONGLOMERATEID \
AND C.TABLEID = COLS.REFERENCEID \
ORDER BY PKTABLE_CAT, \
PKTABLE_SCHEM, \
PKTABLE_NAME, \
PK_NAME, \
KEY_SEQ
S.SCHEMANAME AS PKTABLE_SCHEM, \
TABLENAME AS PKTABLE_NAME, \
COLS.COLUMNNAME AS PKCOLUMN_NAME, \
CAST ('' AS VARCHAR(128)) AS FKTABLE_CAT, \
FKTABLE_SCHEM, \
FKTABLE_NAME, \
FKCOLUMN_NAME, \
CAST (CONGLOMS.DESCRIPTOR.getKeyColumnPosition( \
COLS.COLUMNNUMBER) \
AS SMALLINT) AS KEY_SEQ, \
CAST ((CASE WHEN FK_UPDATERULE='S' \
THEN java.sql.DatabaseMetaData::importedKeyRestrict ELSE \
(CASE WHEN FK_UPDATERULE='R' \
THEN java.sql.DatabaseMetaData::importedKeyNoAction ELSE \
java.sql.DatabaseMetaData::importedKeyNoAction END) END) \
AS SMALLINT) AS UPDATE_RULE, \
CAST ((CASE WHEN FK_DELETERULE='S' \
THEN java.sql.DatabaseMetaData::importedKeyRestrict ELSE \
(CASE WHEN FK_DELETERULE='R' \
THEN java.sql.DatabaseMetaData::importedKeyNoAction ELSE \
(CASE WHEN FK_DELETERULE='C' \
THEN java.sql.DatabaseMetaData::importedKeyCascade ELSE \
(CASE WHEN FK_DELETERULE='U' \
THEN java.sql.DatabaseMetaData::importedKeySetNull ELSE \
java.sql.DatabaseMetaData::importedKeyNoAction END) END) END) END) \
AS SMALLINT) AS DELETE_RULE, \
FK_NAME, \
CONSTRAINTNAME AS PK_NAME, \
CAST (java.sql.DatabaseMetaData::importedKeyNotDeferrable \
AS SMALLINT) AS DEFERRABILITY \
FROM --DERBY-PROPERTIES joinOrder=FIXED \n\
(SELECT F2.keyCONSTRAINTID AS FK_ID, \
FKTB_SCHEMA AS FKTABLE_SCHEM, \
FKTB_NAME AS FKTABLE_NAME, \
COLS2.COLUMNNAME AS FKCOLUMN_NAME, \
CONGLOMS2.DESCRIPTOR.getKeyColumnPosition( \
COLS2.COLUMNNUMBER) AS KEY_SEQ, \
C2.CONSTRAINTNAME AS FK_NAME, \
F2.DELETERULE AS FK_UPDATERULE, \
F2.DELETERULE AS FK_DELETERULE \
FROM --DERBY-PROPERTIES joinOrder=FIXED \n\
(SELECT T2.TABLEID AS FKTB_ID, \
S2.SCHEMANAME AS FKTB_SCHEMA, \
T2.TABLENAME AS FKTB_NAME \
FROM \
SYS.SYSTABLES T2 --DERBY-PROPERTIES index = 'SYSTABLES_INDEX1' \n\
, SYS.SYSSCHEMAS S2 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSSCHEMAS_INDEX1' \n\
WHERE \
((1=1) OR ? IS NOT NULL) \
AND S2.SCHEMANAME LIKE ? \
AND T2.TABLENAME LIKE ? \
AND S2.SCHEMAID = T2.SCHEMAID \
) AS FKTB (FKTB_ID, FKTB_SCHEMA, FKTB_NAME), \
SYS.SYSCONSTRAINTS c2 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCONSTRAINTS_INDEX3' \n\
, SYS.SYSFOREIGNKEYS F2 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSFOREIGNKEYS_INDEX1' \n\
, SYS.SYSCONGLOMERATES CONGLOMS2 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCONGLOMERATES_INDEX1' \n\
, SYS.SYSCOLUMNS COLS2 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCOLUMNS_INDEX1' \n\
WHERE \
FKTB.FKTB_ID = C2.TABLEID \
AND F2.CONSTRAINTID = C2.CONSTRAINTID \
AND FKTB.FKTB_ID = COLS2.REFERENCEID \
AND (CASE WHEN CONGLOMS2.DESCRIPTOR IS NOT NULL THEN \
CONGLOMS2.DESCRIPTOR.getKeyColumnPosition( \
COLS2.COLUMNNUMBER) ELSE \
0 END) <> 0 \
AND F2.CONGLOMERATEID = CONGLOMS2.CONGLOMERATEID \
) AS FKINFO(FK_ID, \
FKTABLE_SCHEM, \
FKTABLE_NAME, \
FKCOLUMN_NAME, \
KEY_SEQ, \
FK_NAME, \
FK_UPDATERULE, \
FK_DELETERULE), \
SYS.SYSCONSTRAINTS c --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCONSTRAINTS_INDEX1' \n\
, SYS.SYSTABLES T --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSTABLES_INDEX2' \n\
, SYS.SYSSCHEMAS S --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSSCHEMAS_INDEX2' \n\
, SYS.SYSKEYS K --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSKEYS_INDEX1' \n\
, SYS.SYSCONGLOMERATES CONGLOMS --DERBY-PROPERTIES joinStrategy = NESTEDLOOP, index = 'SYSCONGLOMERATES_INDEX1' \n\
, SYS.SYSCOLUMNS COLS --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCOLUMNS_INDEX1' \n\
WHERE T.TABLEID = C.TABLEID \
AND C.CONSTRAINTID = FKINFO.FK_ID \
AND FKINFO.KEY_SEQ = CONGLOMS.DESCRIPTOR.getKeyColumnPosition( \
COLS.COLUMNNUMBER) \
AND S.SCHEMAID = T.SCHEMAID \
AND K.CONSTRAINTID = C.CONSTRAINTID \
AND (CASE WHEN CONGLOMS.DESCRIPTOR IS NOT NULL THEN \
CONGLOMS.DESCRIPTOR.getKeyColumnPosition(COLS.COLUMNNUMBER) ELSE 0 END) <> 0 \
AND K.CONGLOMERATEID = CONGLOMS.CONGLOMERATEID \
AND C.TABLEID = COLS.REFERENCEID \
ORDER BY PKTABLE_CAT, \
PKTABLE_SCHEM, \
PKTABLE_NAME, \
PK_NAME, \
KEY_SEQ
thanks,
Mamta
