読者です 読者をやめる 読者になる 読者になる

HHeLiBeXの日記 正道編

日々の記憶の記録とメモ‥

テーブルの制約の規定に使用されている列の取得

SQL DB2 PostgreSQL

ある制約を規定するのに使用されている列を取得するには、KEY_COLUMN_USAGEというビューを使用することができる。
PostgreSQL v8.4で次のようなクエリを発行する:

SELECT *
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'public'
ORDER BY CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, ORDINAL_POSITION

と、キー(primary key、unique constraint、foreign key)に使用されている列名の一覧を得ることができる。(結果は後述)
で、KEY_COLUMN_USAGEビューのポイントは、「foreign keyにおいて参照元の何番目に指定した列が、参照先のprimary key/unique constraintの何番目に指定された列なのか」という対応を、(ORDINAL_POSITION, POSITION_IN_UNIQUE_CONSTRAINT)という2つの列で保持していることである。
DB2にはこのKEY_COLUMN_USAGEビューがないので、なんとか作ってみる(謎)。
調べてみると、SYSIBM.SQLFOREIGNKEYS、SYSCAT.KEYCOLUSEというのがあり、これらとSYSIBM.TABLE_CONSTRAINTSを組み合わせれば望む情報が取れそう。
で、書いてみたクエリが次のような感じ。

WITH KEY_COLUMN_USAGE (
          CONSTRAINT_CATALOG
        , CONSTRAINT_SCHEMA
        , CONSTRAINT_NAME
        , TABLE_CATALOG
        , TABLE_SCHEMA
        , TABLE_NAME
        , COLUMN_NAME
        , ORDINAL_POSITION
        , POSITION_IN_UNIQUE_CONSTRAINT
) AS (
    SELECT 
          CURRENT_SERVER AS CONSTRAINT_CATALOG
        , KCU1.CONSTRAINT_SCHEMA
        , KCU1.CONSTRAINT_NAME
        , CURRENT_SERVER AS TABLE_CATALOG
        , KCU1.TABLE_SCHEMA
        , KCU1.TABLE_NAME
        , KCU1.COLUMN_NAME
        , KCU1.ORDINAL_POSITION
        , FK.KEY_SEQ AS POSITION_IN_UNIQUE_CONSTRAINT
    FROM 
        TABLE(
            SELECT
                  TC.CONSTRAINT_CATALOG
                , TC.CONSTRAINT_SCHEMA
                , TC.CONSTRAINT_NAME
                , TC.TABLE_CATALOG
                , TC.TABLE_SCHEMA
                , TC.TABLE_NAME
                , KCU.COLNAME AS COLUMN_NAME
                , KCU.COLSEQ AS ORDINAL_POSITION
            FROM SYSIBM.TABLE_CONSTRAINTS AS TC
                INNER JOIN SYSCAT.KEYCOLUSE AS KCU
            ON TC.CONSTRAINT_NAME = KCU.CONSTNAME
                AND TC.TABLE_SCHEMA = KCU.TABSCHEMA
                AND TC.TABLE_NAME = KCU.TABNAME
        ) AS KCU1
        LEFT OUTER JOIN SYSIBM.SQLFOREIGNKEYS AS FK
    ON KCU1.TABLE_SCHEMA = FK.FKTABLE_SCHEM
        AND KCU1.TABLE_NAME = FK.FKTABLE_NAME
        AND KCU1.COLUMN_NAME = FK.FKCOLUMN_NAME
        AND KCU1.CONSTRAINT_NAME = FK.FK_NAME
)
SELECT *
FROM KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = CURRENT_SCHEMA
ORDER BY CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, ORDINAL_POSITION

ここで、使用しているビューについて調べてみると、次のことがわかった。

  • SYSIBM.SQLFOREIGNKEYS ビューの KEY_SEQ 列:
    • KEY_COLUMN_USAGE における POSITION_IN_UNIQUE_CONSTRAINT
  • SYSCAT.KEYCOLUSE ビューの COLSEQ 列:
    • KEY_COLUMN_USAGE における POSITION_IN_UNIQUE_CONSTRAINT
      • かと思いきや、foreign key だけは 参照先の primary key/unique constraint の POSITION_IN_UNIQUE_CONSTRAINT になっている。

せめて、SYSCAT.KEYCOLUSE ビューの COLSEQ 列が、どのような順番で列を指定したのかということを表してくれていれば、SYSIBM.SQLFOREIGNKEYS ビューと結合することによって、(ORDINAL_POSITION, POSITION_IN_UNIQUE_CONSTRAINT)を生成することができたのだが‥
ということで、この情報だけは復元できないため、PostgreSQLDB2 ではちょっと違った結果になってしまう。まぁ、ordinal_position を使わないで、ということで(ぇ
最後に、PostgreSQL v8.4 と DB2 v9.7 での実行結果。

PostgreSQL v8.4:
 constraint_catalog | constraint_schema | constraint_name | table_catalog | table_schema | table_name | column_name | ordinal_position | position_in_unique_constraint 
--------------------+-------------------+-----------------+---------------+--------------+------------+-------------+------------------+-------------------------------
 postgres           | public            | pkey_bar        | postgres      | public       | bar        | col1        |                1 |                              
 postgres           | public            | pkey_bar        | postgres      | public       | bar        | col4        |                2 |                              
 postgres           | public            | pkey_foo        | postgres      | public       | foo        | col1        |                1 |                              
 postgres           | public            | pkey_foo        | postgres      | public       | foo        | col4        |                2 |                              
 postgres           | public            | pkey_hoge       | postgres      | public       | hoge       | col1        |                1 |                              
 postgres           | public            | pkey_hoge       | postgres      | public       | hoge       | col2        |                2 |                              
 postgres           | public            | ref_bar1        | postgres      | public       | bar        | col1        |                1 |                             1
 postgres           | public            | ref_bar1        | postgres      | public       | bar        | col4        |                2 |                             2
 postgres           | public            | ref_bar2        | postgres      | public       | bar        | col4        |                1 |                             2
 postgres           | public            | ref_bar2        | postgres      | public       | bar        | col1        |                2 |                             1
 postgres           | public            | ref_foo1        | postgres      | public       | foo        | col1        |                1 |                             2
 postgres           | public            | ref_foo1        | postgres      | public       | foo        | col4        |                2 |                             1
 postgres           | public            | ref_foo2        | postgres      | public       | foo        | col4        |                1 |                             1
 postgres           | public            | ref_foo2        | postgres      | public       | foo        | col1        |                2 |                             2
 postgres           | public            | uniq_bar1       | postgres      | public       | bar        | col1        |                1 |                              
 postgres           | public            | uniq_bar1       | postgres      | public       | bar        | col2        |                2 |                              
 postgres           | public            | uniq_bar2       | postgres      | public       | bar        | col4        |                1 |                              
 postgres           | public            | uniq_bar2       | postgres      | public       | bar        | col2        |                2 |                              
 postgres           | public            | uniq_bar2       | postgres      | public       | bar        | col3        |                3 |                              
 postgres           | public            | uniq_foo1       | postgres      | public       | foo        | col1        |                1 |                              
 postgres           | public            | uniq_foo1       | postgres      | public       | foo        | col2        |                2 |                              
 postgres           | public            | uniq_foo2       | postgres      | public       | foo        | col4        |                1 |                              
 postgres           | public            | uniq_foo2       | postgres      | public       | foo        | col2        |                2 |                              
 postgres           | public            | uniq_foo2       | postgres      | public       | foo        | col3        |                3 |                              
 postgres           | public            | uniq_hoge       | postgres      | public       | hoge       | col3        |                1 |                              
 postgres           | public            | uniq_hoge       | postgres      | public       | hoge       | col1        |                2 |                              

DB2 v9.7:
 CONSTRAINT_CATALOG   CONSTRAINT_SCHEMA   CONSTRAINT_NAME   TABLE_CATALOG   TABLE_SCHEMA   TABLE_NAME   COLUMN_NAME   ORDINAL_POSITION   POSITION_IN_UNIQUE_CONSTRAINT
 ------------------   ------------------- ----------------- --------------- -------------- ------------ ------------- ----------------   -----------------------------
 SANDBOX              DB2ADMIN            PKEY_BAR          SANDBOX         DB2ADMIN       BAR          COL1                         1                               -
 SANDBOX              DB2ADMIN            PKEY_BAR          SANDBOX         DB2ADMIN       BAR          COL4                         2                               -
 SANDBOX              DB2ADMIN            PKEY_FOO          SANDBOX         DB2ADMIN       FOO          COL1                         1                               -
 SANDBOX              DB2ADMIN            PKEY_FOO          SANDBOX         DB2ADMIN       FOO          COL4                         2                               -
 SANDBOX              DB2ADMIN            PKEY_HOGE         SANDBOX         DB2ADMIN       HOGE         COL1                         1                               -
 SANDBOX              DB2ADMIN            PKEY_HOGE         SANDBOX         DB2ADMIN       HOGE         COL2                         2                               -
 SANDBOX              DB2ADMIN            REF_BAR1          SANDBOX         DB2ADMIN       BAR          COL1                         1                               1
 SANDBOX              DB2ADMIN            REF_BAR1          SANDBOX         DB2ADMIN       BAR          COL4                         2                               2
 SANDBOX              DB2ADMIN            REF_BAR2          SANDBOX         DB2ADMIN       BAR          COL1                         1                               1
 SANDBOX              DB2ADMIN            REF_BAR2          SANDBOX         DB2ADMIN       BAR          COL4                         2                               2
 SANDBOX              DB2ADMIN            REF_FOO1          SANDBOX         DB2ADMIN       FOO          COL4                         1                               1
 SANDBOX              DB2ADMIN            REF_FOO1          SANDBOX         DB2ADMIN       FOO          COL1                         2                               2
 SANDBOX              DB2ADMIN            REF_FOO2          SANDBOX         DB2ADMIN       FOO          COL4                         1                               1
 SANDBOX              DB2ADMIN            REF_FOO2          SANDBOX         DB2ADMIN       FOO          COL1                         2                               2
 SANDBOX              DB2ADMIN            UNIQ_BAR1         SANDBOX         DB2ADMIN       BAR          COL1                         1                               -
 SANDBOX              DB2ADMIN            UNIQ_BAR1         SANDBOX         DB2ADMIN       BAR          COL2                         2                               -
 SANDBOX              DB2ADMIN            UNIQ_BAR2         SANDBOX         DB2ADMIN       BAR          COL4                         1                               -
 SANDBOX              DB2ADMIN            UNIQ_BAR2         SANDBOX         DB2ADMIN       BAR          COL2                         2                               -
 SANDBOX              DB2ADMIN            UNIQ_BAR2         SANDBOX         DB2ADMIN       BAR          COL3                         3                               -
 SANDBOX              DB2ADMIN            UNIQ_FOO1         SANDBOX         DB2ADMIN       FOO          COL1                         1                               -
 SANDBOX              DB2ADMIN            UNIQ_FOO1         SANDBOX         DB2ADMIN       FOO          COL2                         2                               -
 SANDBOX              DB2ADMIN            UNIQ_FOO2         SANDBOX         DB2ADMIN       FOO          COL4                         1                               -
 SANDBOX              DB2ADMIN            UNIQ_FOO2         SANDBOX         DB2ADMIN       FOO          COL2                         2                               -
 SANDBOX              DB2ADMIN            UNIQ_FOO2         SANDBOX         DB2ADMIN       FOO          COL3                         3                               -
 SANDBOX              DB2ADMIN            UNIQ_HOGE         SANDBOX         DB2ADMIN       HOGE         COL3                         1                               -
 SANDBOX              DB2ADMIN            UNIQ_HOGE         SANDBOX         DB2ADMIN       HOGE         COL1                         2                               -

まぁ、比較するのも面倒だと思うが、column_name 列と position_in_unique_constraint 列の値の順番が一部異なる(ordinal_positionでソートしているため)。