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

HHeLiBeXの日記 正道編

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

テーブルの制約一覧の取得

おもむろに次のようなテーブルを作ってみる。

CREATE TABLE hoge(
      col1 INT NOT NULL
    , col2 INT NOT NULL
    , CONSTRAINT pkey_hoge PRIMARY KEY(col1, col2)
) ;
CREATE TABLE foo(
      col1 INT NOT NULL
    , col2 INT NOT NULL
    , col3 INT NOT NULL
    , col4 INT NOT NULL
    , CONSTRAINT pkey_foo PRIMARY KEY(col1, col4)
    , CONSTRAINT uniq1 UNIQUE(col1, col2)
    , CONSTRAINT uniq2 UNIQUE(col4, col2, col3)
    , CONSTRAINT ref1 FOREIGN KEY(col1, col4) REFERENCES hoge(col2, col1)
) ;

これらのテーブルに設定された制約の一覧を取得するには、TABLE_CONSTRAINTSというビューを使用することができる。
PostgreSQL v8.4でのクエリ:

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

DB2 v9.7でのクエリ:

SELECT *
FROM SYSIBM.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = CURRENT_SCHEMA
ORDER BY CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME

結果(比較しやすいように、多少加工してある):

PostgreSQL v8.4:
 constraint_catalog | constraint_schema |    constraint_name           | table_catalog | table_schema | table_name | constraint_type | is_deferrable | initially_deferred 
--------------------+-------------------+-----------------------       +---------------+--------------+------------+-----------------+---------------+--------------------
 postgres           | public            | 2200_41050_1_not_null        | postgres      | public       | hoge       | CHECK           | NO            | NO
 postgres           | public            | 2200_41050_2_not_null        | postgres      | public       | hoge       | CHECK           | NO            | NO
 postgres           | public            | 2200_41050_3_not_null        | postgres      | public       | hoge       | CHECK           | NO            | NO
 postgres           | public            | 2200_41057_1_not_null        | postgres      | public       | foo        | CHECK           | NO            | NO
 postgres           | public            | 2200_41057_2_not_null        | postgres      | public       | foo        | CHECK           | NO            | NO
 postgres           | public            | 2200_41057_3_not_null        | postgres      | public       | foo        | CHECK           | NO            | NO
 postgres           | public            | 2200_41057_4_not_null        | postgres      | public       | foo        | CHECK           | NO            | NO
 postgres           | public            | 2200_41076_1_not_null        | postgres      | public       | bar        | CHECK           | NO            | NO
 postgres           | public            | 2200_41076_2_not_null        | postgres      | public       | bar        | CHECK           | NO            | NO
 postgres           | public            | 2200_41076_3_not_null        | postgres      | public       | bar        | CHECK           | NO            | NO
 postgres           | public            | 2200_41076_4_not_null        | postgres      | public       | bar        | CHECK           | NO            | NO
 postgres           | public            | pkey_bar                     | postgres      | public       | bar        | PRIMARY KEY     | NO            | NO
 postgres           | public            | pkey_foo                     | postgres      | public       | foo        | PRIMARY KEY     | NO            | NO
 postgres           | public            | pkey_hoge                    | postgres      | public       | hoge       | PRIMARY KEY     | NO            | NO
 postgres           | public            | ref_bar1                     | postgres      | public       | bar        | FOREIGN KEY     | NO            | NO
 postgres           | public            | ref_bar2                     | postgres      | public       | bar        | FOREIGN KEY     | NO            | NO
 postgres           | public            | ref_foo1                     | postgres      | public       | foo        | FOREIGN KEY     | NO            | NO
 postgres           | public            | ref_foo2                     | postgres      | public       | foo        | FOREIGN KEY     | NO            | NO
 postgres           | public            | uniq_bar1                    | postgres      | public       | bar        | UNIQUE          | NO            | NO
 postgres           | public            | uniq_bar2                    | postgres      | public       | bar        | UNIQUE          | NO            | NO
 postgres           | public            | uniq_foo1                    | postgres      | public       | foo        | UNIQUE          | NO            | NO
 postgres           | public            | uniq_foo2                    | postgres      | public       | foo        | UNIQUE          | NO            | NO
 postgres           | public            | uniq_hoge                    | postgres      | public       | hoge       | UNIQUE          | NO            | NO

DB2 v9.7:
 CONSTRAINT_CATALOG   CONSTRAINT_SCHEMA   CONSTRAINT_NAME                TABLE_CATALOG   TABLE_SCHEMA   TABLE_NAME   CONSTRAINT_TYPE   IS_DEFERRABLE   INITIALLY_DEFERRED
 -------------------- ------------------- ------------------------------ --------------- -------------- ------------ ---------------   -------------   ------------------
 SANDBOX              DB2ADMIN            0528052537572000000004000200   SANDBOX         DB2ADMIN       HOGE         CHECK             NO              NO                
 SANDBOX              DB2ADMIN            0528052537572000010004000200   SANDBOX         DB2ADMIN       HOGE         CHECK             NO              NO                
 SANDBOX              DB2ADMIN            0528052537572000020004000200   SANDBOX         DB2ADMIN       HOGE         CHECK             NO              NO                
 SANDBOX              DB2ADMIN            0528052537660000000005000200   SANDBOX         DB2ADMIN       FOO          CHECK             NO              NO                
 SANDBOX              DB2ADMIN            0528052537660000010005000200   SANDBOX         DB2ADMIN       FOO          CHECK             NO              NO                
 SANDBOX              DB2ADMIN            0528052537660000020005000200   SANDBOX         DB2ADMIN       FOO          CHECK             NO              NO                
 SANDBOX              DB2ADMIN            0528052537660000030005000200   SANDBOX         DB2ADMIN       FOO          CHECK             NO              NO                
 SANDBOX              DB2ADMIN            0528052537844000000006000200   SANDBOX         DB2ADMIN       BAR          CHECK             NO              NO                
 SANDBOX              DB2ADMIN            0528052537844000010006000200   SANDBOX         DB2ADMIN       BAR          CHECK             NO              NO                
 SANDBOX              DB2ADMIN            0528052537844000020006000200   SANDBOX         DB2ADMIN       BAR          CHECK             NO              NO                
 SANDBOX              DB2ADMIN            0528052537844000030006000200   SANDBOX         DB2ADMIN       BAR          CHECK             NO              NO                
 SANDBOX              DB2ADMIN            PKEY_BAR                       SANDBOX         DB2ADMIN       BAR           PRIMARY KEY      NO              NO                
 SANDBOX              DB2ADMIN            PKEY_FOO                       SANDBOX         DB2ADMIN       FOO           PRIMARY KEY      NO              NO                
 SANDBOX              DB2ADMIN            PKEY_HOGE                      SANDBOX         DB2ADMIN       HOGE          PRIMARY KEY      NO              NO                
 SANDBOX              DB2ADMIN            REF_BAR1                       SANDBOX         DB2ADMIN       BAR           FOREIGN KEY      NO              NO                
 SANDBOX              DB2ADMIN            REF_BAR2                       SANDBOX         DB2ADMIN       BAR           FOREIGN KEY      NO              NO                
 SANDBOX              DB2ADMIN            REF_FOO1                       SANDBOX         DB2ADMIN       FOO           FOREIGN KEY      NO              NO                
 SANDBOX              DB2ADMIN            REF_FOO2                       SANDBOX         DB2ADMIN       FOO           FOREIGN KEY      NO              NO                
 SANDBOX              DB2ADMIN            UNIQ_BAR1                      SANDBOX         DB2ADMIN       BAR           UNIQUE           NO              NO                
 SANDBOX              DB2ADMIN            UNIQ_BAR2                      SANDBOX         DB2ADMIN       BAR           UNIQUE           NO              NO                
 SANDBOX              DB2ADMIN            UNIQ_FOO1                      SANDBOX         DB2ADMIN       FOO           UNIQUE           NO              NO                
 SANDBOX              DB2ADMIN            UNIQ_FOO2                      SANDBOX         DB2ADMIN       FOO           UNIQUE           NO              NO                
 SANDBOX              DB2ADMIN            UNIQ_HOGE                      SANDBOX         DB2ADMIN       HOGE          UNIQUE           NO              NO                

PostgreSQLDB2での違いを以下に。

  • カタログ名、スキーマ名、制約名、テーブル名が、PostgreSQLは小文字、DB2は大文字。
    • 一応、標準SQL(SQL2003)ではcase-normal formはupper caseとなっているが、昔は明記されていなかったのかもしれない(知らないけど)
  • 制約タイプ(CONSTRAINT_TYPE)の値が、DB2の場合だけ先頭に空白が入っているものがある。(この空白はなんだろう‥)