MySQL 表中的同一个字段能否同时是两个表的外键

yydy1983 2019-08-07

展开全文

stackflow 上面找到一个不错的答案，翻译一下记录下来。

Q：
有三个表：regions，countries，states。countries和states都是regions的一部分。regions是食物链的最顶端。
现在要加一个popular_area表，表中有两个字段：region_id和popular_place_id。是否能将popular_place_id表示成countries或states的外键？大概还需要添加一个popular_place_type列来表示popular_place_id是country的id还是state的id。

A：
你所描述的被称为多态关联（Polymorphic Associations）：外键列表示的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 等 ORM 框架支持。但这些框架也明确表示应该禁用 SQL 约束以使用此功能。相反，应用程序或框架必须做相应的工作以确保引用得到满足。也就是说，外键的值存在于一个可能的目标表中。

多态关联在执行数据库一致性方面很弱。数据完整性依赖于所有访问数据库的客户机都具有相同的参照完整性逻辑，而且强制执行必须无 bug。

这里有一些可以利用数据库强制参照完整性的替代解决方案：

为每一个目标表创建一个额外的表
比如，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),
);

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

创建一个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)
);

就关系理论而言，多态关联违背了第一范式，因为popular_place_id实际上是具有两个含义的列：可以是state也可以是country。你不会将一个人的年龄和电话号码存储在一个列中，出于同样的原因，你不应该在单个列中存储state_id和country_id。这两个属性具有兼容的数据类型这一事实是巧合的；它们仍然表示不同的逻辑实体。

多态关联同样违背了第三范式，因为列的含义依赖于外键引用的表的其他列，而不是主键列。在第三范式中，表中的属性必须仅依赖于其他表的主键。