mysql多态关联_多态关联 - 可以对两个可能的表中的一个执行MySQL外键吗？

您所描述的内容称为多态关联。 也就是说，“外键”列包含必须存在于一组目标表之一中的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和Products，分别参考Products和phone_number。 这些“流行”表中的每一个也引用用户的配置文件。

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

);

这意味着要获得所有用户最喜欢的地方，您需要查询这两个表。 但这意味着您可以依靠数据库来强制实现一致性。

创建一个Products表作为超级表。 正如Abie所提到的，第二种选择是你的热门地点引用像Products这样的表，它是Products和phone_number的父。也就是说，州和国家也有state_id的外键(你甚至可以制作这个外键 也是country_id和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)

);

使用两列。 可以使用两列，而不是可以引用两个目标表中的任何一列。 这两列可能是Products; 实际上只有其中一个应该是非Products。

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)

);

在关系理论方面，多态关联违反了第一范式，因为Products实际上是一个有两个含义的列：它是一个州或一个国家。 您不会将一个人的Products及其phone_number存储在一个列中，出于同样的原因，您不应将state_id和country_id存储在一列中。 这两个属性具有兼容的数据类型的事实是巧合的; 它们仍然表示不同的逻辑实体。

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

来自@SavasVedova的评论：

我不确定在没有看到表定义或示例查询的情况下遵循您的描述，但听起来您只是有多个Products表，每个表都包含一个引用中央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的东西只会作为一行空值返回。 但是您仍然必须对所有连接的表进行硬编码，如果添加新的过滤表，则必须更新代码。

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)

...

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