PostgreSQLで複合主キー参照外部キー制約を作成する方法

2024-05-15

PostgreSQLにおける複合主キー参照外部キー制約

例

次の例は、orders と products という 2 つのテーブルを示しています。

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  product_id INT NOT NULL,
  quantity INT NOT NULL,
  FOREIGN KEY (product_id) REFERENCES products(product_id)
);

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  product_name VARCHAR(255) NOT NULL
);

この例では、orders テーブルの product_id 列は products テーブルの product_id 列を参照する外部キー制約です。つまり、orders テーブルに挿入される product_id は、常に products テーブルに存在する product_id でなければなりません。

複合主キー参照

orders テーブルの主キーは order_id 列のみですが、product_id 列も products テーブルとの関連を定義するために使用できます。この場合、product_id 列を部分複合主キーとみなすことができます。

同様のことを、products テーブルの product_name 列のようなその他の列にも適用できます。

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  product_id INT NOT NULL,
  product_name VARCHAR(255) NOT NULL,
  FOREIGN KEY (product_id, product_name) REFERENCES products(product_id, product_name)
);

この例では、orders テーブルの product_id 列と product_name 列の組み合わせが複合主キーとなり、products テーブルの対応する列を参照する外部キー制約となります。

利点

複合主キー参照外部キー制約を使用する利点は次のとおりです。

データ整合性を保証します。
関連データ間の参照を容易にします。
データベース設計の柔軟性を高めます。

注意事項

参照される側のテーブルの主キーが常に一意であることを確認する必要があります。
更新や削除操作が関連データに与える影響を考慮する必要があります。

PostgreSQLにおける複合主キー参照外部キー制約は、関連データ間の整合性を保証し、データベース設計の柔軟性を高める強力なツールです。上記の例と注意事項を参考に、適切な状況で使用してください。

PostgreSQLにおける複合主キー参照外部キー制約 - サンプルコード

-- products テーブルを作成
CREATE TABLE products (
  product_id INT PRIMARY KEY,
  product_name VARCHAR(255) NOT NULL
);

-- orders テーブルを作成
CREATE TABLE orders (
  order_id INT NOT NULL,
  product_id INT NOT NULL,
  quantity INT NOT NULL,
  PRIMARY KEY (order_id, product_id),
  FOREIGN KEY (product_id) REFERENCES products(product_id)
);

このコードを実行すると、次のようになります。

products テーブルには、product_id 列 (主キー) と product_name 列が作成されます。
orders テーブルには、order_id 列、product_id 列、quantity 列、および複合主キー (order_id と product_id) が作成されます。

例:

以下の例は、products テーブルにデータを追加し、orders テーブルに product_id を参照するレコードを挿入する方法を示しています。

-- products テーブルにデータを追加
INSERT INTO products (product_id, product_name)
VALUES
  (1, 'T-Shirt'),
  (2, 'Pants'),
  (3, 'Hat');

-- orders テーブルにレコードを挿入
INSERT INTO orders (order_id, product_id, quantity)
VALUES
  (1, 1, 2),
  (2, 2, 1),
  (3, 3, 5);

products テーブルには、3 つのレコードが挿入されます。
orders テーブルには、3 つのレコードが挿入されます。各レコードの product_id は、products テーブルに存在する product_id を参照しています。

外部キー制約の確認:

以下のコマンドを使用して、orders テーブルの外部キー制約を確認できます。

SHOW CREATE TABLE orders;

出力結果の一部は以下のようになります。

...
CONSTRAINT fk_orders_products FOREIGN KEY (product_id) REFERENCES products(product_id)...

このサンプルコードは、複合主キー参照外部キー制約の基本的な使用方法を示すものです。実際の使用例では、状況に合わせてコードを調整する必要があります。

サブクエリを使用して参照列を指定する

この方法は、参照される列が動的に変化する場合に役立ちます。

CREATE TABLE orders (
  order_id INT NOT NULL,
  product_id INT NOT NULL,
  quantity INT NOT NULL,
  PRIMARY KEY (order_id, product_id),
  FOREIGN KEY (product_id) REFERENCES products(product_id)
    MATCH FULL
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
);

上記の例では、MATCH FULL オプションを使用して、orders テーブルの product_id と quantity 列の値が、products テーブルの対応する列の値と完全に一致することを確認しています。

部分一致の外部キー制約を作成する

CREATE TABLE orders (
  order_id INT NOT NULL,
  product_id INT NOT NULL,
  product_name VARCHAR(255) NOT NULL,
  PRIMARY KEY (order_id, product_id, product_name),
  FOREIGN KEY (product_id, product_name) REFERENCES products(product_id, product_name)
    MATCH PARTIAL
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
);