当外键可以关联到不同的表时,如何保持数据库的一致性[重复]

你所描述的是多态关联。也就是说,“外键”列包含在一组目标表中必须存在的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 表作为超级表。 地点 ,它是两者的父级 国家 和 国家 . 也就是说,国家和国家都有 地点 (您甚至可以将此外键也设置为 国家 和 国家 ).

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 ;事实上,其中只有一个不应该- 无效的 .

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要求在编写查询时指定并修复表。不能根据在 产品 .

唯一的另一个选择是加入 全部的 使用外部联接筛选表。那些没有匹配的产品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)
...

但是这种格式仍然要求您编写对所有表的引用。这是绕不过去的。

这不是世界上最优雅的解决方案,但是您可以使用 concrete table inheritance 让这一切顺利。

从概念上讲,你提出了一个“可以成为流行区域的事物”的概念,你的三种类型的地方从中继承。您可以将其表示为一个表,例如, places 其中每一行与 regions , countries ,或 states . (在地区、国家或州之间共享的属性(如果有的话)可以推送到这个places表中。) popular_place_id 然后将是对places表中一行的外键引用,该行将引导您找到一个地区、国家或州。

您在第二篇专栏文章中提出的描述关联类型的解决方案碰巧是Rails如何处理多态关联,但我一般不喜欢这样。比尔非常详细地解释了为什么多态关联不是你的朋友。

下面是对Bill Karwin的“supertable”方法的修正,使用了一个复合键 ( 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还没有上市。

我意识到这根线是旧的,但我看到这个,我想到了一个解决办法,我想我会把它扔在那里。

区域、国家和州是生活在等级制度中的地理位置。

通过创建一个名为geographic_location_type的域表,您可以完全避免问题,该表将填充三行(Region、Country、State)。

接下来,不要创建三个位置表,而是创建一个具有geographic_location_type_id外键的geographic_location表(这样您就知道实例是地区、国家还是州)。

通过使此表自引用来建模层次结构,以便状态实例将fKey保存到其父国家/地区实例,其父国家/地区实例又将fKey保存到其父区域实例。区域实例在该fKey中将保留空值。这和你用三张表做的没有什么不同(你会有一个-很多地区和国家之间的关系,国家和州之间的关系),除非现在它都在一张表中。

流行的用户位置表是用户和地理位置之间的范围分辨率表(因此许多用户可能喜欢许多地方)。

苏乌

CREATE TABLE [geographical_location_type] (
    [geographical_location_type_id] INTEGER NOT NULL,
    [name] VARCHAR(25) NOT NULL,
    CONSTRAINT [PK_geographical_location_type] PRIMARY KEY ([geographical_location_type_id])
)

-- Add 'Region', 'Country' and 'State' instances to the above table


CREATE TABLE [geographical_location] (
   [geographical_location_id] BIGINT IDENTITY(0,1) NOT NULL,
    [name] VARCHAR(1024) NOT NULL,
    [geographical_location_type_id] INTEGER NOT NULL,
    [geographical_location_parent] BIGINT,  -- self referencing; can be null for top-level instances
    CONSTRAINT [PK_geographical_location] PRIMARY KEY ([geographical_location_id])
)

CREATE TABLE [user] (
    [user_id] BIGINT NOT NULL,
    [login_id] VARCHAR(30) NOT NULL,
    [password] VARCHAR(512) NOT NULL,
    CONSTRAINT [PK_user] PRIMARY KEY ([user_id])
)


CREATE TABLE [popular_user_location] (
    [popular_user_location_id] BIGINT NOT NULL,
    [user_id] BIGINT NOT NULL,
    [geographical_location_id] BIGINT NOT NULL,
    CONSTRAINT [PK_popular_user_location] PRIMARY KEY ([popular_user_location_id])
)

ALTER TABLE [geographical_location] ADD CONSTRAINT [geographical_location_type_geographical_location] 
    FOREIGN KEY ([geographical_location_type_id]) REFERENCES [geographical_location_type] ([geographical_location_type_id])



ALTER TABLE [geographical_location] ADD CONSTRAINT [geographical_location_geographical_location] 
    FOREIGN KEY ([geographical_location_parent]) REFERENCES [geographical_location] ([geographical_location_id])



ALTER TABLE [popular_user_location] ADD CONSTRAINT [user_popular_user_location] 
    FOREIGN KEY ([user_id]) REFERENCES [user] ([user_id])



ALTER TABLE [popular_user_location] ADD CONSTRAINT [geographical_location_popular_user_location] 
    FOREIGN KEY ([geographical_location_id]) REFERENCES [geographical_location] ([geographical_location_id])

不确定目标数据库是什么;上面是MS SQL Server。