【データベース管理者必見】PostgreSQLで外部キーをサッと一覧表示する方法

PostgreSQLで外部キーの一覧を表示する方法

\d+ テーブル名 コマンド

\d+ テーブル名

                              Foreign Keys
Table      |   Column   | Foreign Key | References
-----------+-----------+-------------+------------
orders     | customer_id | orders_fk   | customers(id)

• Table: 外部キーを持つテーブル名
• Column: 外部キー列名
• Foreign Key: 外部キー制約名
• References: 参照先のテーブル名(括弧内に参照先の列名)

information_schema ビュー

information_schema スキーマには、データベースに関するメタデータが格納されています。外部キーに関する情報は、KEY_COLUMN_USAGE ビューで確認できます。

SELECT
  REFERENCED_TABLE_NAME AS 参照先テーブル名,
  REFERENCED_COLUMN_NAME AS 参照先列名,
  TABLE_NAME AS テーブル名,
  COLUMN_NAME AS 列名
FROM information_schema.KEY_COLUMN_USAGE
WHERE REFERENCED_COLUMN_NAME IS NOT NULL
AND TABLE_NAME = 'テーブル名';

• 参照先テーブル名: 参照先のテーブル名
• 参照先列名: 参照先の列名
• 列名: 外部キー列名

pgAdmin などのツール

pgAdmin などのデータベース管理ツールを使用すると、GUI上で外部キーを簡単に一覧表示できます。

PostgreSQLでテーブルの外部キーを一覧表示するには、いくつかの方法があります。それぞれの方法にはメリットとデメリットがあるので、状況に合わせて使い分けてください。

• \d+ テーブル名 コマンド: シンプルで使いやすい
• information_schema ビュー: 詳細な情報を取得できる
• pgAdmin などのツール: GUI上で操作できる

• 外部キーは、異なるテーブル間の関連性を定義するために使用されます。
• 外部キー制約を設定することで、データの整合性を保つことができます。

-- テーブル作成
CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers (id)
);

-- 外部キーの一覧表示

-- 1. `\d+ テーブル名` コマンド
\d+ orders

-- 2. `information_schema` ビュー
SELECT
  REFERENCED_TABLE_NAME AS 参照先テーブル名,
  REFERENCED_COLUMN_NAME AS 参照先列名,
  TABLE_NAME AS テーブル名,
  COLUMN_NAME AS 列名
FROM information_schema.KEY_COLUMN_USAGE
WHERE REFERENCED_COLUMN_NAME IS NOT NULL
AND TABLE_NAME = 'orders';

-- 3. pgAdmin などのツール
-- pgAdmin などのツールを使用する場合は、GUI上で操作できます。

外部キー制約の確認

-- 外部キー制約に違反するデータ挿入
INSERT INTO orders (customer_id) VALUES (100);

-- エラーが発生
ERROR:  insert or update on table "orders" violates foreign key constraint "orders_fk"
DETAIL:  Key (customer_id)=(100) is not present in table "customers".

ALTER TABLE orders
DROP CONSTRAINT orders_fk;

psql コマンドの \dt オプション

\dt テーブル名

-- 外部キーの情報も表示される

information_schema ビューの REFERENTIAL_CONSTRAINTS ビュー

SELECT
  CONSTRAINT_NAME AS 外部キー制約名,
  TABLE_NAME AS テーブル名,
  COLUMN_NAME AS 列名,
  REFERENCED_TABLE_NAME AS 参照先テーブル名,
  REFERENCED_COLUMN_NAME AS 参照先列名
FROM information_schema.REFERENTIAL_CONSTRAINTS
WHERE TABLE_NAME = 'テーブル名';

外部キー制約を直接参照

SELECT * FROM pg_constraint
WHERE contype = 'f'
AND conrelid = 'テーブルのOID';

-- テーブルのOIDは `\d+ テーブル名` コマンドで確認できる