[解決済み] PostgreSQLで多対多の関係を実装するには?

質問

タイトルは自明だと思います。PostgreSQLで多対多の関係を作るには、どのようにテーブル構造を作ればよいのでしょうか。

私の例です。

Product(name, price);
Bill(name, date, Products);

どのように解決するのですか？

SQLのDDL（データ定義言語）ステートメントは、次のようになります。

CREATE TABLE product (
  product_id serial PRIMARY KEY  -- implicit primary key constraint
, product    text NOT NULL
, price      numeric NOT NULL DEFAULT 0
);

CREATE TABLE bill (
  bill_id  serial PRIMARY KEY
, bill     text NOT NULL
, billdate date NOT NULL DEFAULT CURRENT_DATE
);

CREATE TABLE bill_product (
  bill_id    int REFERENCES bill (bill_id) ON UPDATE CASCADE ON DELETE CASCADE
, product_id int REFERENCES product (product_id) ON UPDATE CASCADE
, amount     numeric NOT NULL DEFAULT 1
, CONSTRAINT bill_product_pkey PRIMARY KEY (bill_id, product_id)  -- explicit pk
);

少し調整しました。

• は n:mの関係 は通常、別のテーブルで実装されています。 bill_product によって実装されます。

• 私が追加した serial というカラムを 代理の主キー . Postgres 10 以降では IDENTITY カラム の代わりに 参照してください。

  • シリアル主キーカラムを使用したテーブルの安全な名前変更
  • テーブルのカラムを自動インクリメントする
  • https://www.2ndquadrant.com/en/blog/postgresql-10-identity-columns/

  製品名はほとんど一意ではないので、私はそれを強くお勧めします (良い "自然キー" ではありません)。また、一意性を強制し、外部キーでカラムを参照することは、通常、4 バイトの integer (あるいは 8 バイトの bigint として格納された文字列よりも text または varchar .

• のような基本的なデータ型の名前は使用しないでください。 date として 識別子 . これは可能ですが、スタイルが悪く、エラーやエラーメッセージを混乱させることになります。使用方法 合法的な、小文字の、引用符で囲まれていない識別子 . 決して 予約語 を使わないでください。また、大文字と小文字が混在したダブルクォートの識別子はできるだけ避けてください。

• "name"は良い名前ではありません。テーブルのカラムの名前を変更しました。 product に変更しました。 product (または product_name など）。これは、より良い という命名規則です。 . そうしないと、クエリでいくつかのテーブルを結合するとき - つまり 多くの場合 という名前のカラムが複数存在することになり、カラムのエイリアスを使用して混乱を整理しなければならなくなります。これは便利ではありません。もう 1 つの広範なアンチパターンは、カラム名として単に "id" を使用することです。
  
  の名前を bill になるのでしょう。 bill_id はこの場合、おそらく十分でしょう。

• price は データ型 numeric 分数を格納するための 入力されたとおりに正確に (浮動小数点型ではなく任意精度型)。もし整数だけを扱うのであれば，それを integer . 例えば 価格をCentsとして .

• は amount ( "Products" はリンクテーブルの中に入ります。 bill_product であり、タイプは numeric という型でもあります。また integer というように、整数だけを扱う場合は

• を見ます。 外部キー で bill_product ? どちらも変更をカスケードするために作成しました。 ON UPDATE CASCADE . もし product_id または bill_id が変更されると、その変更は bill_product のすべての依存するエントリにカスケードされ、何も壊れません。これらは、それ自体の重要性を持たない単なる参照です。
  
  また、私は ON DELETE CASCADE に対して bill_id : 請求書が削除されると、その詳細も一緒に削除されます。
  
  商品の場合はそうではない。請求書に使用されている製品を削除することはできません。これを行おうとすると、Postgresはエラーを出します。そこで、別のカラムを product に別の列を追加し、廃止された行をマークします（"soft-delete")。

• この基本的な例におけるすべてのカラムは、最終的に NOT NULL というように NULL の値は許可されません。(はい。 すべて カラム - 主キーカラムが定義されている UNIQUE NOT NULL を自動生成します)。というのも NULL の値は、どの列でも意味をなさないからです。初心者はこれで楽になる。しかし、そう簡単にはいかない、理解する必要があるのは NULL ハンドリング のように、とにかく 追加のカラムによって NULL 値、関数、結合を導入することができます。 NULL 値を導入することができます。

• の章を読んでください。 CREATE TABLE マニュアルにある .

• 主キーの実装は、一意の インデックス で実装されており、PK カラムに対する条件付きのクエリを高速に実行できます。しかし、複数カラムのキーではキーカラムの順序が重要になる。PKは bill_product にある PK は (bill_id, product_id) に別のインデックスを追加することができます。 product_id あるいは (product_id, bill_id) を探すクエリがある場合、指定した product_id を探すクエリがあり bill_id . 見てください。

  • PostgreSQLの複合主キー
  • 複合インデックスは、最初のフィールドに対するクエリにも有効ですか？
  • PostgreSQLにおけるインデックスの動作

• を読む マニュアルのインデックスに関する章を読んでください。 .