当前位置: 首页 > 面试题库 >

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

张晨朗
2023-03-14
问题内容

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

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


问题答案:

您所描述的称为多态关联。也就是说,“外键”列包含一个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_statespopular_countries,分别引用statescountries。这些“受欢迎”表中的每一个也都引用用户的个人资料。

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,这是父母双方statescountries。也就是说,这两个州和国家也有外键places(你甚至可以使这个外键也是主键statescountries)。

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_idcountry_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的元素将仅作为单行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)
...

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



 类似资料:
  • 问题内容: 我使用python创建原型,并且为此使用了zip函数,但不确定如何在Java中执行此操作。基本上,我有两个列表(一个是名称,一个是数据),希望它们相对于彼此排序。我的程序仅处理列表(在这种情况下为数据),但是我将名称用作对正在处理的数据的引用,并且我想尝试以不同的顺序处理数据。这是结构的一个示例(实际上我的数据没有提供给我存储,但是我会对其进行基本排序或反向排序,没什么花哨的)。 所以

  • 问题内容: 我有一个表,其中有几个ID列指向其他表。 我希望 只有 在将数据放入其中时,外键才能强制完整性。如果我稍后进行更新以填充该列,则它还应该检查约束。 (这可能取决于数据库服务器,我使用的是MySQL和InnoDB表类型) 我相信这是一个合理的期望,但是如果我错了,请纠正我。 问题答案: 是的,您只能在值不为NULL时强制执行约束。可以使用以下示例轻松测试它: 第一次插入将通过,因为我们在

  • 问题内容: 假设提供了以下映射: Java类: 是否可以更改Hibernate映射,以便 在启动时仍由Hibernate强制并创建外键 ,但类如下所示: 我知道,如果我将其转换为a 可以使用,但是数据库不会强制使用外键。 我需要执行此操作,因为对象可能会(或可能不会)单独初始化,这有时会导致 在调用时发生异常。我希望将其作为,并在必要时加载整个对象。这也将使对象的加载更快。 我相信我的问题与这一问

  • 在MySQL中,我有3个表:Client、Room和ClientRoom,它指向前面的两个表。 使用python manage.py inspectdb 问题是,要创建ClientRoom对象,我需要Client和Room对象的id,而不是两个对象:一个Client和一个Room。 这对我来说是没有意义的,而且这也阻止了进一步的进展。我一直在做gui站点(在HTML上),它显示房间号,应该显示客户

  • 问题内容: 我目前正在为我们团队的项目设计数据库结构。我目前在脑海中有一个非常疑问的问题:能否将外键用作另一个表上的主键? 以下是我们系统的数据库设计表: 我想做的是,该表应包含学生用户和指导顾问用户的ID(应该是系统的登录凭据)和密码。简而言之,和表的主键也是表的外键。但我不确定是否允许。 另一个问题是:一个表中也存在,这需要一个(其为中表)和一个(这也是在)它的每个记录的。如果学生和辅导员的I

  • 我有两张桌子: 用户(用户名、密码) 配置文件(profileId,gender,dateofbirding,...) 目前我正在使用这种方法:每个Profile记录都有一个名为“userid”的字段作为外键,它链接到用户表。当用户注册时,他的配置文件记录将自动创建。 我对我朋友的建议感到困惑:将“userid”字段作为外部和主键,并删除“profileid”字段。哪种方法更好?