可以为两个可能的表之一做一个MySQL外键吗？

那么这是我的问题我有三张桌子; 地区,国家,国家.国家可以在区域内,州可以在区域内.地区是食物链的顶端.

现在我添加一个包含两列的popular_areas表; region_id和popular_place_id.是否有可能使popular_place_id成为国家或州的外键.我可能不得不添加一个popular_place_type列来确定id是否描述了一个国家或州.

1> Bill Karwin..：

---

您所描述的内容称为多态关联.也就是说,"外键"列包含必须存在于一组目标表之一中的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表作为超级表. 由于艾比提到,第二个选择是你的热闹的地方引用一个表像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)
);

在关系理论方面,多态关联违反了第一范式,因为popular_place_id它实际上是一个有两个含义的列:它是一个国家或一个国家.你不会存储个人的age和他们phone_number在一列,出于同样的原因,你不应该同时存储state_id和country_id在单个列.这两个属性具有兼容的数据类型的事实是巧合的; 它们仍然表示不同的逻辑实体.

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

---

来自@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的东西只会作为一行空值返回.但是您仍然必须对所有连接的表进行硬编码,如果添加新的过滤表,则必须更新代码.

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

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

---

我真的很喜欢"CONSTRAINT CHECK".但如果我们将"OR"改为"XOR",它可以得到改善.这样我们确保set中只有一列是NOT NULL

2> Abie..：

---

这不是世界上最优雅的解决方案,但您可以使用具体的表继承来实现此功能.

从概念上讲,你提出了一类"可以成为流行区域的东西"的概念,你的三种类型的地方都可以从中继承.你可以代表这是一个被称为表,例如,places其中每行中有一个排的一对一的关系regions,countries或states.(区域,国家或州之间共享的属性,如果有的话,可以推送到此places表中.)popular_place_id然后,您将成为places表中行的外键引用,然后引导您进入某个地区,国家/地区或州.

你提出的第二列描述关联类型的解决方案恰好是Rails如何处理多态关联,但我不是一般的粉丝.Bill详细解释了为什么多态关联不是你的朋友.

3> onedaywhen..：

---

这是对Bill Karwin的"超级"方法的修正,使用复合键( place_type, place_id )来解决感知到的正常形式违规:

CREATE TABLE places (
  place_id INT NOT NULL UNIQUE,
  place_type VARCHAR(10) NOT NULL
     CHECK ( place_type = 'state', 'country' ),
  UNIQUE ( place_type, place_id )
);

CREATE TABLE states (
  place_id INT NOT NULL UNIQUE,
  place_type VARCHAR(10) DEFAULT 'state' NOT NULL
     CHECK ( place_type = 'state' ),
  FOREIGN KEY ( place_type, place_id ) 
     REFERENCES places ( place_type, place_id )
  -- attributes specific to states go here
);

CREATE TABLE countries (
  place_id INT NOT NULL UNIQUE,
  place_type VARCHAR(10) DEFAULT 'country' NOT NULL
     CHECK ( place_type = 'country' ),
  FOREIGN KEY ( place_type, place_id ) 
     REFERENCES places ( place_type, place_id )
  -- attributes specific to country go here
);

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

这种设计无法确保每行中places存在一行states或countries(但不是两者).这是SQL中外键的限制.在完全符合SQL-92标准的DBMS中,您可以定义可延迟的表间约束,这些约束允许您实现相同但却很笨重,涉及事务并且这样的DBMS尚未将其推向市场.