MySQL 表中的同一個字段能否同時是兩個表的外鍵

yydy1983 2019-08-07

展開全文

stackflow 上面找到一個不錯的答案，翻譯一下記錄下來。

Q：
有三個表：regions，countries，states。countries和states都是regions的一部分。regions是食物鏈的最頂端。
現(xiàn)在要加一個popular_area表，表中有兩個字段：region_id和popular_place_id。是否能將popular_place_id表示成countries或states的外鍵？大概還需要添加一個popular_place_type列來表示popular_place_id是country的id還是state的id。

A：
你所描述的被稱為多態(tài)關聯(lián)（Polymorphic Associations）：外鍵列表示的id存在于一組目標表中的一個。通常，目標表在某種程度上是相關的，例如某些常見的超類數(shù)據(jù)的實例。除了這個外鍵列，你還需要另外一個字段，用來指定引用的目標表。

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 約束來建模多態(tài)關聯(lián)的方法，因為外鍵約束總是引用一個目標表。

多態(tài)關聯(lián)被 Rails 和 Hibernate 等 ORM 框架支持。但這些框架也明確表示應該禁用 SQL 約束以使用此功能。相反，應用程序或框架必須做相應的工作以確保引用得到滿足。也就是說，外鍵的值存在于一個可能的目標表中。

多態(tài)關聯(lián)在執(zhí)行數(shù)據(jù)庫一致性方面很弱。數(shù)據(jù)完整性依賴于所有訪問數(shù)據(jù)庫的客戶機都具有相同的參照完整性邏輯，而且強制執(zhí)行必須無 bug。

這里有一些可以利用數(shù)據(jù)庫強制參照完整性的替代解決方案：

為每一個目標表創(chuàng)建一個額外的表
比如，popular_states和popular_contries，分別引用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),
);

這意味著要獲得所有用戶最喜歡的地方，您需要查詢這兩個表。但這意味著您可以依賴數(shù)據(jù)庫來執(zhí)行一致性。

創(chuàng)建一個place表作為超表
popular_areas可以引用一個表，比如places，同時place也是states表和countries表的父表。也就是說，這兩個都有一個對places的外鍵（你甚至可以讓這兩個表的外鍵成為主鍵）。

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)
);

使用兩個列作為外鍵
使用兩列而不是一個可以引用兩個目標表的列。這兩列可以為null；實際上應該有一列為non-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)
);

就關系理論而言，多態(tài)關聯(lián)違背了第一范式，因為popular_place_id實際上是具有兩個含義的列：可以是state也可以是country。你不會將一個人的年齡和電話號碼存儲在一個列中，出于同樣的原因，你不應該在單個列中存儲state_id和country_id。這兩個屬性具有兼容的數(shù)據(jù)類型這一事實是巧合的；它們仍然表示不同的邏輯實體。

多態(tài)關聯(lián)同樣違背了第三范式，因為列的含義依賴于外鍵引用的表的其他列，而不是主鍵列。在第三范式中，表中的屬性必須僅依賴于其他表的主鍵。