テーブルの制約一覧の取得
おもむろに次のようなテーブルを作ってみる。
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
PostgreSQLとDB2での違いを以下に。
- カタログ名、スキーマ名、制約名、テーブル名が、PostgreSQLは小文字、DB2は大文字。
- 一応、標準SQL(SQL2003)ではcase-normal formはupper caseとなっているが、昔は明記されていなかったのかもしれない(知らないけど)
- 制約タイプ(CONSTRAINT_TYPE)の値が、DB2の場合だけ先頭に空白が入っているものがある。(この空白はなんだろう‥)
