mysql - 可以对两个可能的表之一执行MySQL外键吗？

Question

好吧，这是我的问题，我有三个桌子。地区，国家，州。国家可以在区域内部，州可以在区域内部。地区是食物链的顶端。

现在，我添加一个带有两列的Popular_areas表； region_id和Popular_place_id。是否有可能使Popular_place_id成为任何国家或州的外键。我可能将不得不添加一个Popular_place_type列，以确定ID是否以任何一种方式描述一个国家或州。

Answer 1

您所描述的称为多态关联。也就是说，“外键”列包含一个ID值，该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约束为多态关联建模。外键约束始终引用一个目标表。

Rails和Hibernate等框架支持多态关联。但是他们明确表示必须禁用SQL约束才能使用此功能。而是，应用程序或框架必须做等效的工作以确保满足引用。即，外键中的值存在于可能的目标表之一中。

多态关联在强制数据库一致性方面较弱。数据完整性取决于使用强制实施相同参照完整性逻辑的所有客户端访问数据库，并且强制实施必须没有错误。

这是一些利用数据库强制引用完整性的替代解决方案：

每个目标额外创建一张表。例如 popular_states和 popular_countries，它们分别引用 states和 countries。这些“受欢迎”表中的每一个也引用用户的个人资料。

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表作为超级表。正如Abie所提到的，第二种替代方法是，您的热门场所引用 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)
);

使用两列。代替可能引用两个目标表之一的一列，请使用两列。这两列可能是 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)
);

根据关系理论，多态关联违反了 First Normal Form，因为 popular_place_id实际上是一列，具有两个含义：它是州或国家。您不会将某人的 age和他们的 phone_number存储在单个列中，并且出于相同的原因，您也不应将 state_id和 country_id都存储在单个列中。这两个属性具有兼容的数据类型这一事实是偶然的；它们仍然表示不同的逻辑实体。

多态关联也违反了 Third Normal Form，因为列的含义取决于为外键引用的表命名的额外列。在“第三范式”中，表中的属性必须仅取决于该表的主键。



来自@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的元素将仅作为单行null返回。但是您仍然必须对所有联接表进行硬编码，并且如果添加新的过滤器表，则必须更新代码。

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

但是这种格式仍然需要您编写对所有表的引用。没有解决的办法。