テーブルの制約の規定に使用されている列の取得
ある制約を規定するのに使用されている列を取得するには、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)という２つの列で保持していることである。
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 になっている。
- KEY_COLUMN_USAGE における POSITION_IN_UNIQUE_CONSTRAINT
せめて、SYSCAT.KEYCOLUSE ビューの COLSEQ 列が、どのような順番で列を指定したのかということを表してくれていれば、SYSIBM.SQLFOREIGNKEYS ビューと結合することによって、(ORDINAL_POSITION, POSITION_IN_UNIQUE_CONSTRAINT)を生成することができたのだが‥
ということで、この情報だけは復元できないため、PostgreSQL と DB2 ではちょっと違った結果になってしまう。まぁ、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でソートしているため)。
