Tihiroの頭を休めるIT教室

少しだけ頭使って後は根性

PostgreSQLでテーブルの制約を確認する。

概要

前回(PostgreSQLでテーブルの定義を確認する。 - Tihiroの頭を休めるIT教室)はPostgreSQLでテーブル定義を確認する、ということでした。 今回は、テーブルの制約を確認したいと思います。

例によって環境は

  • PostgreSQL9.6

です。

確認してみる

メタコマンドで確認してみる

前回、テーブル定義を確認したのと同じく

¥d テーブル名

で確認できます。便利ですね!

SELECT文で確認してみる

メタコマンドとは違って、SELECT文で制約を確認するのは少しめんどいです。なぜなら、情報スキーマ内の複数のビューを参照する必要があるからです。

めんどいことは細かく分解して解決するのが一番です。

ということで、

  1. 主キー制約
  2. CHECK制約
  3. 外部キー制約
  4. ユニーク制約

の4つに分けて確認してみます。

1.SELECT文で主キー制約を確認する

テーブルの主キー*1を確認するSELECT文です。

SELECT
    tb_con.table_catalog AS "データベース名"
  , tb_con.table_name AS "テーブル名"
  , kcu.ordinal_position AS "主キーの順番"
  , ccu.column_name AS "カラム名"
FROM
  information_schema.table_constraints tb_con
    INNER JOIN information_schema.constraint_column_usage ccu
            ON tb_con.constraint_catalog = ccu.constraint_catalog
           AND tb_con.constraint_schema = ccu.constraint_schema
           AND tb_con.constraint_name = ccu.constraint_name
    INNER JOIN information_schema.key_column_usage kcu
            ON tb_con.constraint_catalog = kcu.constraint_catalog
           AND tb_con.constraint_schema = kcu.constraint_schema
           AND tb_con.constraint_name = kcu.constraint_name
           AND ccu.column_name = kcu.column_name
WHERE
      tb_con.table_name = 'テーブル名'
  AND tb_con.constraint_type = 'PRIMARY KEY'
ORDER BY
    tb_con.table_catalog
  , tb_con.table_name
  , tb_con.constraint_name
  , kcu.ordinal_position

2.SELECT文でCHECK制約を確認する

テーブルのCHECK制約を確認するSELECT文です。表示されるのは主にNOT NULLです。

SELECT
    tb_con.table_catalog AS "データベース名"
  , tb_con.table_name AS "テーブル名"
  , tb_con.constraint_name AS "制約名"
  , ch_con.check_clause AS "チェック制約の内容"
FROM
  information_schema.table_constraints tb_con
    INNER JOIN information_schema.check_constraints ch_con
            ON tb_con.constraint_catalog = ch_con.constraint_catalog
           AND tb_con.constraint_schema = ch_con.constraint_schema
           AND tb_con.constraint_name = ch_con.constraint_name
WHERE
      tb_con.table_name = 'テーブル名'
  AND tb_con.constraint_type = 'CHECK'
ORDER BY
    tb_con.table_catalog
  , tb_con.table_name
  , tb_con.constraint_name

3.SELECT文で外部キー制約を確認する

便利ですが、設計が甘いと不便になる外部キー制約を確認するSELECT文です。

SELECT
    tb_con.table_catalog AS "データベース名"
  , tb_con.table_name AS "参照テーブル名"
  , kcu.column_name AS "参照カラム名"
  , ref_tb_con.table_name AS "被参照テーブル名"
  , ref_ccu.column_name AS "被参照カラム名"
  , kcu.ordinal_position AS "順番"
  , tb_con.constraint_name AS "ユニーク制約の名前"
FROM
  information_schema.table_constraints tb_con
    INNER JOIN information_schema.key_column_usage kcu
            ON tb_con.constraint_catalog = kcu.constraint_catalog
           AND tb_con.constraint_schema = kcu.constraint_schema
           AND tb_con.constraint_name = kcu.constraint_name
    INNER JOIN information_schema.referential_constraints ref_con
            ON tb_con.constraint_catalog = ref_con.constraint_catalog
           AND tb_con.constraint_schema = ref_con.constraint_schema
           AND tb_con.constraint_name = ref_con.constraint_name
    INNER JOIN information_schema.table_constraints ref_tb_con
            ON ref_con.unique_constraint_catalog = ref_tb_con.constraint_catalog
           AND ref_con.unique_constraint_schema = ref_tb_con.constraint_schema
           AND ref_con.unique_constraint_name = ref_tb_con.constraint_name
    INNER JOIN information_schema.constraint_column_usage ref_ccu
            ON ref_tb_con.constraint_catalog = ref_ccu.constraint_catalog
           AND ref_tb_con.constraint_schema = ref_ccu.constraint_schema
           AND ref_tb_con.constraint_name = ref_ccu.constraint_name
    INNER JOIN information_schema.key_column_usage ref_kcu
            ON ref_tb_con.constraint_catalog = ref_kcu.constraint_catalog
           AND ref_tb_con.constraint_schema = ref_kcu.constraint_schema
           AND ref_tb_con.constraint_name = ref_kcu.constraint_name
           AND ref_ccu.column_name = ref_kcu.column_name
           AND kcu.ordinal_position = ref_kcu.ordinal_position
WHERE
      tb_con.table_name = 'テーブル名'
  AND tb_con.constraint_type = 'FOREIGN KEY'
ORDER BY
    tb_con.table_catalog
  , tb_con.table_name
  , tb_con.constraint_name
  , kcu.ordinal_position

4.SELECT文でユニーク制約を確認する

ユニーク制約を確認するSELECT文です。ユニーク制約自体の使い所としては「外部キー制約の参照元にしたいカラムに設定する」ということが多いのではないでしょうか。

SELECT
    tb_con.table_catalog AS "データベース名"
  , tb_con.table_name AS "テーブル名"
  , ccu.column_name AS "カラム名"
  , kcu.ordinal_position AS "ユニーク制約の順番"
  , tb_con.constraint_name AS "ユニーク制約の名前"
FROM
  information_schema.table_constraints tb_con
    INNER JOIN information_schema.constraint_column_usage ccu
            ON tb_con.constraint_catalog = ccu.constraint_catalog
           AND tb_con.constraint_schema = ccu.constraint_schema
           AND tb_con.constraint_name = ccu.constraint_name
    INNER JOIN information_schema.key_column_usage kcu
            ON tb_con.constraint_catalog = kcu.constraint_catalog
           AND tb_con.constraint_schema = kcu.constraint_schema
           AND tb_con.constraint_name = kcu.constraint_name
           AND ccu.column_name = kcu.column_name
WHERE
      tb_con.table_name = 'テーブル名'
  AND tb_con.constraint_type = 'UNIQUE'
ORDER BY
    tb_con.table_catalog
  , tb_con.table_name
  , tb_con.constraint_name
  , kcu.ordinal_position

ちなみに

システムカタログのpg_constraintpg_attributeで、今回確認した制約やらのことがわかるというらしいですが、よく分かりませんでした。

また、排他制約とかいうのも理解できていないですし、使いそうにもないので調査対象外となっております。悪しからずご了承くださいませ。

まとめ

以上、若干長くなりましたがPostgreSQLでテーブルの制約を確認する方法でした。

SELECT文で確認する方法は面倒ですね。例によってFunction化しておくのがよいかもしれません。

*1:プライマリーキーとも呼ばれます