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

#1 楼

您所描述的称为多态关联。也就是说，“外键”列包含一个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_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表作为超级表。正如Abie所提到的，第二种选择是您的热门场所引用了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的元素将仅作为单行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)
...

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

#2 楼

这不是世界上最优雅的解决方案，但是您可以使用具体的表继承来完成这项工作。

从概念上讲，您正在提出一类“可以成为流行区域的事物”的概念，这是您的三种类型的场所所继承的。您可以将其表示为一个名为places的表，其中每行与regions，countries或states中的一行具有一对一的关系。 （可以将在区域，国家或州之间共享的属性（如果有的话，推入此位置表）。）然后，您的popular_place_id将成为对位置表中某行的外键引用，然后将您引至某个区域，国家或州。

您建议在第二栏中描述关联类型的解决方案恰好是Rails处理多态关联的方式，但是我一般不喜欢这种方式。 Bill详细解释了为什么多态关联不是您的朋友。

#3 楼

这是对比尔·卡尔文（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尚未投放市场。

#4 楼

我意识到这个线程很旧，但是我看到了这个想法，想到了一个解决方案，我想我应该把它扔在那里。

地区，国家和州是生活在层次结构中的地理位置。

您可以通过创建一个称为geo_location_type的域表来完全避免问题，该域表将填充三行（Region，Country，State）。

接下来，而不是三个位置表，创建一个具有geo_location_type_id外键的单一geo_location表（这样您就知道实例是Region，Country还是State）。

通过使该表自引用来对层次结构进行建模，以使State实例将fKey保留为其父Country实例，该国家实例又将fKey保留为其父Region实例。区域实例在该fKey中将保留NULL。这与您对这三个表（您将拥有1 –区域与国家之间以及国家与州之间的许多关系）所做的操作没有什么不同，只不过现在全部都在一个表中了。

popular_user_location该表将是用户和georgraphical_location之间的范围解析表（许多用户可能喜欢很多地方）。

Soooo…

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。

#5 楼

好吧，我有两个表：


歌曲

a）歌曲编号
b）歌曲标题
.....


播放列表
a）播放列表编号
b）播放列表标题
...

我有第三个


songs_to_playlist_relation

问题是某些播放列表链接到其他播放列表。但是在mysql中，我们没有与两个表相关联的外键。

我的解决方案：我将在第三行放置在songs_to_playlist_relation中。该列将为布尔值。如果为1，则为歌曲，否则将链接到播放列表表。

因此：


songs_to_playlist_relation

a）播放列表编号（int）
b）是歌曲（布尔值）
c）相对编号（歌曲编号或播放列表编号）（整数）（不是任何表的外键）

 #create table songs
    queries.append("SET SQL_MODE = NO_AUTO_VALUE_ON_ZERO;")
    queries.append("CREATE TABLE songs (NUMBER int(11) NOT NULL,SONG POSITION int(11) NOT NULL,PLAY SONG tinyint(1) NOT NULL DEFAULT '1',SONG TITLE varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,DESCRIPTION varchar(1000) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,ARTIST varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'Άγνωστος καλλιτέχνης',AUTHOR varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'Άγνωστος στιχουργός',COMPOSER varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'Άγνωστος συνθέτης',ALBUM varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'Άγνωστο άλμπουμ',YEAR int(11) NOT NULL DEFAULT '33',RATING int(11) NOT NULL DEFAULT '5',IMAGE varchar(600) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,SONG PATH varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,SONG REPEAT int(11) NOT NULL DEFAULT '0',VOLUME float NOT NULL DEFAULT '1',SPEED float NOT NULL DEFAULT '1') ENGINE=InnoDB DEFAULT CHARSET=utf8;")
    queries.append("ALTER TABLE songs ADD PRIMARY KEY (NUMBER), ADD UNIQUE KEY POSITION (SONG POSITION), ADD UNIQUE KEY TITLE (SONG TITLE), ADD UNIQUE KEY PATH (SONG PATH);")
    queries.append("ALTER TABLE songs MODIFY NUMBER int(11) NOT NULL AUTO_INCREMENT;")

#create table playlists
queries.append("CREATE TABLE `playlists` (`NUMBER` int(11) NOT NULL,`PLAYLIST POSITION` int(11) NOT NULL,`PLAYLIST TITLE` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,`PLAYLIST PATH` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;")
queries.append("ALTER TABLE `playlists` ADD PRIMARY KEY (`NUMBER`),ADD UNIQUE KEY `POSITION` (`PLAYLIST POSITION`),ADD UNIQUE KEY `TITLE` (`PLAYLIST TITLE`),ADD UNIQUE KEY `PATH` (`PLAYLIST PATH`);")
queries.append("ALTER TABLE `playlists` MODIFY `NUMBER` int(11) NOT NULL AUTO_INCREMENT;")

#create table for songs to playlist relation
queries.append("CREATE TABLE `songs of playlist` (`PLAYLIST NUMBER` int(11) NOT NULL,`SONG OR PLAYLIST` tinyint(1) NOT NULL DEFAULT '1',`RELATIVE NUMBER` int(11) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;")
queries.append("ALTER TABLE `songs of playlist` ADD KEY `PLAYLIST NUMBER` (`PLAYLIST NUMBER`) USING BTREE;")
queries.append("ALTER TABLE `songs of playlist` ADD CONSTRAINT `playlist of playlist_ibfk_1` FOREIGN KEY (`PLAYLIST NUMBER`) REFERENCES `playlists` (`NUMBER`) ON DELETE RESTRICT ON UPDATE RESTRICT")
 
 
 

就这样！

playlists_query = "SELECT s1.*, s3.*, s4.* FROM songs as s1 INNER JOIN `songs of playlist` as s2 ON s1.`NUMBER` = s2.`RELATIVE NUMBER` INNER JOIN `playlists` as s3 ON s3.`NUMBER` = s2.`PLAYLIST NUMBER` INNER JOIN `playlists` as s4 ON s4.`NUMBER` = s2.`RELATIVE NUMBER` ORDER BY s3.`PLAYLIST POSITION`,`s1`.`SONG POSITION`"