INFORMATION_SCHEMAビューを使用して外部キー関係を取得する

2024-04-07

SQLでテーブルの外部キー関係を照会する方法

この解説では、SQLを使用してテーブル間の外部キー関係を照会する方法について説明します。具体的には、以下の方法を解説します。

JOIN句を使用して、関連するテーブルを結合する方法
外部キー制約を使用して、データの整合性を保つ方法
FOREIGN KEY制約を使用して、外部キー関係を定義する方法

前提条件

この解説を理解するには、以下の知識が必要です。

SQLの基本的な構文
テーブルとリレーションシップの概念
外部キー制約の概念

使用するツール

この解説では、以下のツールを使用します。

Oracle Database
SQL Developer

手順

テーブルの作成

まず、以下の2つのテーブルを作成します。

-- 顧客テーブル
CREATE TABLE customers (
  customer_id NUMBER(10) PRIMARY KEY,
  name VARCHAR2(50),
  email VARCHAR2(50)
);

-- 注文テーブル
CREATE TABLE orders (
  order_id NUMBER(10) PRIMARY KEY,
  customer_id NUMBER(10),
  order_date DATE
);

ordersテーブルのcustomer_id列に、customersテーブルのcustomer_id列を外部キーとして設定します。

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers
FOREIGN KEY (customer_id)
REFERENCES customers (customer_id);

JOIN句を使用したテーブルの結合

JOIN句を使用して、customersテーブルとordersテーブルを結合し、顧客情報と注文情報を一緒に表示することができます。

SELECT
  c.name,
  o.order_date
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

このクエリは、customersテーブルとordersテーブルをcustomer_id列で結合し、顧客の名前と注文日を表示します。

ordersテーブルに存在しないcustomer_idを挿入しようとすると、外部キー制約違反エラーが発生します。

INSERT INTO orders (order_id, customer_id, order_date)
VALUES (100, 9999, '2023-12-31');

-- エラー: ORA-02292: 主キーまたは一意制約が違反されました

この解説では、SQLを使用してテーブル間の外部キー関係を照会する方法について説明しました。JOIN句を使用して関連するテーブルを結合し、外部キー制約を使用してデータの整合性を保つことができます。

-- テーブルの作成

CREATE TABLE customers (
  customer_id NUMBER(10) PRIMARY KEY,
  name VARCHAR2(50),
  email VARCHAR2(50)
);

CREATE TABLE orders (
  order_id NUMBER(10) PRIMARY KEY,
  customer_id NUMBER(10),
  order_date DATE
);

-- 外部キー制約の定義

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers
FOREIGN KEY (customer_id)
REFERENCES customers (customer_id);

-- JOIN句を使用したテーブルの結合

SELECT
  c.name,
  o.order_date
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

-- 外部キー制約の確認

INSERT INTO orders (order_id, customer_id, order_date)
VALUES (100, 9999, '2023-12-31');

-- エラー: ORA-02292: 主キーまたは一意制約が違反されました

このコードを実行すると、以下の結果が出力されます。

NAME      ORDER_DATE
--------- --------
田中太郎 2023-12-01
佐藤花子 2023-12-02

ERROR at line 1:
ORA-02292: 主キーまたは一意制約が違反されました。

解説

CREATE TABLE ステートメントを使用して、customersテーブルとordersテーブルを作成します。
ALTER TABLE ステートメントを使用して、ordersテーブルのcustomer_id列に外部キー制約を定義します。
SELECT ステートメントを使用して、JOIN句でcustomersテーブルとordersテーブルを結合し、顧客の名前と注文日を表示します。
INSERT ステートメントを使用して、ordersテーブルに新しいレコードを挿入しようとします。
外部キー制約違反エラーが発生します。

テーブルの外部キー関係を照会するその他の方法

INFORMATION_SCHEMA ビューには、データベース内のすべてのテーブルと列に関する情報が含まれています。以下のビューを使用して、外部キー関係を取得することができます。

REFERENTIAL_CONSTRAINTS ビュー: 外部キー制約に関する情報を表示します。
KEY_COLUMN_USAGE ビュー: 外部キーと主キーの関係に関する情報を表示します。

SELECT
  tc.table_name,
  cc.column_name,
  rc.referenced_table_name,
  rc.referenced_column_name
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
ON rc.constraint_name = tc.constraint_name
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE cc
ON rc.constraint_name = cc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY';

このクエリは、REFERENTIAL_CONSTRAINTS ビュー、TABLE_CONSTRAINTS ビュー、KEY_COLUMN_USAGE ビューを結合して、すべての外部キー関係を取得します。

外部キー制約にトリガーを設定することで、外部キー関係の変更を監視することができます。

CREATE TRIGGER trg_orders_customer_id
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM customers
    WHERE customer_id = :new.customer_id
  ) THEN
    RAISE_EXCEPTION('存在しない顧客IDです。');
  END IF;
END;