[解決済み] MySQLの外部キーを2つの可能なテーブルのうちの1つにすることは可能ですか？

質問

地域、国、州の3つのテーブルがあります。 国は地域の中にあり、州は地域の中にある。 地域は食物連鎖の頂点にある。

現在、region_id と popular_place_id の2つのカラムを持つ popular_areas テーブルを追加しています。 popular_place_id を国名や地域名の外部キーにすることは可能ですか? または の状態です。 おそらくpopular_place_typeカラムを追加して、idが国か州のどちらを記述しているのかを判断する必要がありそうです。

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

あなたが説明しているのは、ポリモーフィック・アソシエーションと呼ばれるものです。 つまり、quot;外部キー"カラムは、一連のターゲット・テーブルのいずれかに存在する必要があるid値を含んでいます。 通常、ターゲットテーブルは、データの共通のスーパークラスのインスタンスであるなど、何らかの形で関連しています。 また、外部キーカラムの横にもうひとつカラムが必要で、各行でどのターゲットテーブルを参照しているかを指定できるようにします。

CREATE TABLE popular_places (
  user_id INT NOT NULL,
  place_id INT NOT NULL,
  place_type VARCHAR(10) -- either 'states' or 'countries'
  -- foreign key is not possible
);

SQL制約を使用してPolymorphic Associationをモデル化する方法はありません。 外部キー制約は常に 1 対象テーブル

ポリモーフィック・アソシエーションは、Rails や Hibernate などのフレームワークでサポートされています。 しかし、彼らはこの機能を使うにはSQL制約を無効にしなければならないと明示的に言っています。 その代わり、アプリケーションやフレームワークは、参照が満たされるように同等の作業を行わなければなりません。 つまり、外部キーの値が、可能なターゲットテーブルのいずれかに存在することです。

ポリモーフィック・アソシエーションは、データベースの一貫性を強制する点では弱点があります。 データの整合性は、同じ参照整合性ロジックが適用されたデータベースにアクセスするすべてのクライアントに依存し、また、その適用にはバグがないことが必要です。

ここでは、データベースで強制される参照整合性を利用した、いくつかの代替案を紹介します。

ターゲットごとに1つの余分なテーブルを作成する。 例えば popular_states と popular_countries を参照する。 states と countries をそれぞれ作成しました。 これらの "popular" テーブルはそれぞれ、ユーザーのプロファイルも参照しています。

CREATE TABLE popular_states (
  state_id INT NOT NULL,
  user_id  INT NOT NULL,
  PRIMARY KEY(state_id, user_id),
  FOREIGN KEY (state_id) REFERENCES states(state_id),
  FOREIGN KEY (user_id) REFERENCES users(user_id),
);

CREATE TABLE popular_countries (
  country_id INT NOT NULL,
  user_id    INT NOT NULL,
  PRIMARY KEY(country_id, user_id),
  FOREIGN KEY (country_id) REFERENCES countries(country_id),
  FOREIGN KEY (user_id) REFERENCES users(user_id),
);

つまり、あるユーザーの人気のあるお気に入りの場所をすべて取得するには、これらのテーブルの両方に問い合わせる必要があるのです。 しかし、これは一貫性を保つためにデータベースに頼ることができることを意味します。

を作成します。 places テーブルをスーパーテーブルとして使用します。 アビーが言及しているように、第二の選択肢は、あなたの人気のある場所が、以下のようなテーブルを参照することです。 places の親であり states と countries . つまり、国家と国の両方が、外部キーとして places (の主キーにすることもできます）。 states と countries ).

CREATE TABLE popular_areas (
  user_id INT NOT NULL,
  place_id INT NOT NULL,
  PRIMARY KEY (user_id, place_id),
  FOREIGN KEY (place_id) REFERENCES places(place_id)
);

CREATE TABLE states (
  state_id INT NOT NULL PRIMARY KEY,
  FOREIGN KEY (state_id) REFERENCES places(place_id)
);

CREATE TABLE countries (
  country_id INT NOT NULL PRIMARY KEY,
  FOREIGN KEY (country_id) REFERENCES places(place_id)
);

2カラムを使用する。 2つのターゲット・テーブルのいずれかを参照することができる1つのカラムの代わりに、2つのカラムを使用します。 これらの2つのカラムは NULL しかし、実際にはどちらか一方だけが非 NULL .

CREATE TABLE popular_areas (
  place_id SERIAL PRIMARY KEY,
  user_id INT NOT NULL,
  state_id INT,
  country_id INT,
  CONSTRAINT UNIQUE (user_id, state_id, country_id), -- UNIQUE permits NULLs
  CONSTRAINT CHECK (state_id IS NOT NULL OR country_id IS NOT NULL),
  FOREIGN KEY (state_id) REFERENCES places(place_id),
  FOREIGN KEY (country_id) REFERENCES places(place_id)
);

関係論の観点からは、ポリモーフィック・アソシエーションは以下の点に違反します。 第一正規形 というのは popular_place_id は事実上2つの意味を持つカラムです：州か国かです。 ある人の age とその phone_number を1つのカラムに格納してはいけません。 state_id と country_id を1つのカラムに含めることができます。 この2つの属性が互換性のあるデータ型を持っているのは偶然で、異なる論理的実体を意味することに変わりはありません。

ポリモーフィック・アソシエーションもまた、次のような違反があります。 第三正規形 なぜなら、そのカラムの意味は、外部キーが参照するテーブルを指定する余分なカラムに依存するからです。 第三正規形では、あるテーブルの属性はそのテーブルの主キーにのみ依存しなければなりません。

SavasVedova さんのコメントを再掲します。

テーブルの定義やクエリの例を見ないと、あなたの説明に納得できないのですが、単に複数の Filters テーブルを参照する外部キーが含まれています。 Products テーブルを使用します。

CREATE TABLE Products (
  product_id INT PRIMARY KEY
);

CREATE TABLE FiltersType1 (
  filter_id INT PRIMARY KEY,
  product_id INT NOT NULL,
  FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

CREATE TABLE FiltersType2 (
  filter_id INT  PRIMARY KEY,
  product_id INT NOT NULL,
  FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

...and other filter tables...

商品を特定のタイプのフィルターに結合することは、どのタイプに結合したいかがわかっていれば簡単です。

SELECT * FROM Products
INNER JOIN FiltersType2 USING (product_id)

フィルタの種類を動的にしたい場合は、SQL クエリを構築するためのアプリケーションコードを書く必要があります。SQL では、クエリを書く時点でテーブルが指定され、固定されていることが必要です。の個々の行にある値に基づいて、結合テーブルを動的に選択させることはできません。 Products .

への結合しかありません。 すべて は外部結合を使用してテーブルをフィルタリングします。一致するproduct_idがないものは、1行のヌルとして返されます。しかし、まだ、ハードコードで すべて また、新しいフィルタ・テーブルを追加する場合は、コードを更新する必要があります。

SELECT * FROM Products
LEFT OUTER JOIN FiltersType1 USING (product_id)
LEFT OUTER JOIN FiltersType2 USING (product_id)
LEFT OUTER JOIN FiltersType3 USING (product_id)
...

すべてのフィルター・テーブルに結合するもう一つの方法は、直列に行うことです。

SELECT * FROM Product
INNER JOIN FiltersType1 USING (product_id)
UNION ALL
SELECT * FROM Products
INNER JOIN FiltersType2 USING (product_id)
UNION ALL
SELECT * FROM Products
INNER JOIN FiltersType3 USING (product_id)
...

しかし、このフォーマットでも、すべてのテーブルへの参照を記述する必要があります。それを回避することはできません。