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

Possible to do a MySQL foreign key to one of two possible tables?

提问by Andrew G. Johnson

Well here's my problem I have three tables; regions, countries, states. Countries can be inside of regions, states can be inside of regions. Regions are the top of the food chain.

嗯，这是我的问题，我有三张桌子；地区、国家、州。国家可以在区域内，国家可以在区域内。区域是食物链的顶端。

Now I'm adding a popular_areas table with two columns; region_id and popular_place_id. Is it possible to make popular_place_id be a foreign key to either countries ORstates. I'm probably going to have to add a popular_place_type column to determine whether the id is describing a country or state either way.

现在我要添加一个包含两列的流行区域表；region_id 和popular_place_id。是否有可能使popular_place_id 成为国家或州的外键。我可能不得不添加一个popular_place_type 列来确定id 是否在描述一个国家或州。

回答by Bill Karwin

What you're describing is called Polymorphic Associations. That is, the "foreign key" column contains an id value that must exist in one of a set of target tables. Typically the target tables are related in some way, such as being instances of some common superclass of data. You'd also need another column along side the foreign key column, so that on each row, you can designate which target table is referenced.

您所描述的称为多态关联。即，“外键”列包含必须存在于一组目标表之一中的 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
);

There's no way to model Polymorphic Associations using SQL constraints. A foreign key constraint always references onetarget table.

无法使用 SQL 约束对多态关联建模。外键约束总是引用一个目标表。

Polymorphic Associations are supported by frameworks such as Rails and Hibernate. But they explicitly say that you must disable SQL constraints to use this feature. Instead, the application or framework must do equivalent work to ensure that the reference is satisfied. That is, the value in the foreign key is present in one of the possible target tables.

多态关联得到 Rails 和 Hibernate 等框架的支持。但是他们明确表示您必须禁用 SQL 约束才能使用此功能。相反，应用程序或框架必须做等效的工作以确保满足引用。也就是说，外键中的值存在于可能的目标表之一中。

Polymorphic Associations are weak with respect to enforcing database consistency. The data integrity depends on all clients accessing the database with the same referential integrity logic enforced, and also the enforcement must be bug-free.

多态关联在强制执行数据库一致性方面很弱。数据完整性取决于所有访问数据库的客户端都执行相同的参照完整性逻辑，并且执行必须没有错误。

Here are some alternative solutions that do take advantage of database-enforced referential integrity:

以下是一些利用数据库强制参照完整性的替代解决方案：

Create one extra table per target.For example popular_statesand popular_countries, which reference statesand countriesrespectively. Each of these "popular" tables also reference the user's profile.

每个目标创建一个额外的表。例如popular_statesand 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),
);

This does mean that to get all of a user's popular favorite places you need to query both of these tables. But it means you can rely on the database to enforce consistency.

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

Create a placestable as a supertable.As Abie mentions, a second alternative is that your popular places reference a table like places, which is a parent to both statesand countries. That is, both states and countries also have a foreign key to places(you can even make this foreign key also be the primary key of statesand countries).

创建一个places表作为超级表。由于艾比提到，第二个选择是你的热闹的地方引用类似的表格places，这是父母双方states和countries。也就是说，states 和 countries 也有一个 to 的外键places（你甚至可以让这个外键也成为statesand的主键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)
);

Use two columns.Instead of one column that may reference either of two target tables, use two columns. These two columns may be NULL; in fact only one of them should be non-NULL.

使用两列。使用两列，而不是可能引用两个目标表之一的列。这两列可能是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)
);

In terms of relational theory, Polymorphic Associations violates First Normal Form, because the popular_place_idis in effect a column with two meanings: it's either a state or a country. You wouldn't store a person's ageand their phone_numberin a single column, and for the same reason you shouldn't store both state_idand country_idin a single column. The fact that these two attributes have compatible data types is coincidental; they still signify different logical entities.

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

Polymorphic Associations also violates Third Normal Form, because the meaning of the column depends on the extra column which names the table to which the foreign key refers. In Third Normal Form, an attribute in a table must depend only on the primary key of that table.

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

---

---

Re comment from @SavasVedova:

来自@SavasVedova 的评论：

I'm not sure I follow your description without seeing the table definitions or an example query, but it sounds like you simply have multiple Filterstables, each containing a foreign key that references a central Productstable.

我不确定我是否在没有看到表定义或示例查询的情况下遵循您的描述，但听起来您只是有多个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...

Joining the products to a specific type of filter is easy if you know which type you want to join to:

如果您知道要加入的类型，则将产品加入特定类型的过滤器很容易：

SELECT * FROM Products
INNER JOIN FiltersType2 USING (product_id)

If you want the filter type to be dynamic, you must write application code to construct the SQL query. SQL requires that the table be specified and fixed at the time you write the query. You can't make the joined table be chosen dynamically based on the values found in individual rows of Products.

如果您希望过滤器类型是动态的，则必须编写应用程序代码来构造 SQL 查询。SQL 要求在您编写查询时指定并修复该表。您不能根据在Products.

The only other option is to join to allfilter tables using outer joins. Those that have no matching product_id will just be returned as a single row of nulls. But you still have to hardcode allthe joined tables, and if you add new filter tables, you have to update your code.

唯一的其他选择是使用外连接连接到所有过滤器表。那些没有匹配 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)
...

Another way to join to all filter tables is to do it serially:

连接到所有过滤器表的另一种方法是按顺序进行：

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

But this format still requires you to write references to all tables. There's no getting around that.

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

回答by Abie

This isn't the most elegant solution in the world, but you could use concrete table inheritanceto make this work.

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

Conceptually you are proposing a notion of a class of "things that can be popular areas" from which your three types of places inherit. You could represent this as a table called, for example, placeswhere each row has a one-to-one relationship with a row in regions, countries, or states. (Attributes that are shared between regions, countries, or states, if any, could be pushed into this places table.) Your popular_place_idwould then be a foreign key reference to a row in the places table which would then lead you to a region, country, or state.

从概念上讲，您提出了一类“可以成为流行区域的事物”的概念，您的三种类型的地点都从该概念继承。你可以代表这是一个被称为表，例如，places其中每行有一个排的一对一的关系regions，countries或states。（在地区、国家或州之间共享的属性，如果有的话，可以被推入这个地方表。）popular_place_id然后你将成为对地方表中一行的外键引用，然后将你带到一个地区，国家，或状态。

The solution you propose with a second column to describe the type of association happens to be how Rails handles polymorphic associations, but I'm not a fan of that in general. Bill explains in excellent detail why polymorphic associations are not your friends.

您在第二列中提出的用于描述关联类型的解决方案恰好是 Rails 处理多态关联的方式，但我一般不喜欢这种方式。Bill 非常详细地解释了为什么多态关联不是你的朋友。

回答by onedaywhen

Here is a correction to Bill Karwin's "supertable" approach, using a compound key ( place_type, place_id )to resolve the perceived normal form violations:

这是对 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 )
);

What this design cannot ensure that for every row in placesthere exists a row in statesor countries(but not both). This is a limitations of foreign keys in SQL. In a full SQL-92 Standards compliant DBMS you could define deferrable inter-table constraints that would allow you to achieve the same but it is clunky, involves transaction and such a DBMS has yet to make it to market.

这种设计不能确保每行都places存在于statesor 中countries（但不能同时存在）。这是 SQL 中外键的局限性。在完全符合 SQL-92 标准的 DBMS 中，您可以定义可延迟的表间约束，这将允许您实现相同的功能，但它很笨重，涉及事务，并且此类 DBMS 尚未投放市场。

回答by Toolsmythe

I realize that this thread is old, but I saw this and a solution came to mind and I thought I'd throw it out there.

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

Regions, Countries and States are Geographical Locations that live in a hierarchy.

地区、国家和州是存在于层次结构中的地理位置。

You could avoid your problem altogether by creating a domain table called geographical_location_type which you would populate with three rows ( Region, Country, State).

您可以通过创建一个名为 geography_location_type 的域表来完全避免您的问题，您将使用三行（地区、国家、州）填充该表。

Next, instead of the three location tables, create a single geographical_location table that has a foreign key of geographical_location_type_id (so you know if the instance is a Region, Country or State).

接下来，不是三个位置表，而是创建一个具有 geo_location_type_id 外键的 geo_location 表（以便您知道实例是区域、国家或州）。

Model the hierarchy by making this table self-referencing so that a State instance holds the fKey to its parent Country instance which in turn holds the fKey to its parent Region instance. Region instances would hold NULL in that fKey. This is no different than what you would have done with the three tables (you would have 1 - many relationships between region and country and between country and state) except now it's all in one table.

通过使该表自引用来对层次结构建模，以便 State 实例将 fKey 保存到其父 Country 实例，而 Country 实例又将 fKey 保存到其父 Region 实例。区域实例将在该 fKey 中保存 NULL。这与您对三个表所做的没有什么不同（您将有 1 - 地区和国家之间以及国家和州之间的许多关系），除了现在它们都在一张表中。

The popular_user_location table would be a scope resolution table between user and georgraphical_location (so many users could like many places).

popular_user_location 表将是 user 和 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])

Wasn't sure what the target DB was; the above is MS SQL Server.

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

回答by Chris P

Well, i have two tables:

好吧，我有两个表：

1. songs

1. 歌曲

a) Song number b) Song title ....

a) 歌曲编号 b) 歌曲名称 ....

1. playlists a) Playlist number b) Playlist title ...

2. 播放列表 a) 播放列表编号 b) 播放列表标题 ...

and i have an third

我有第三个

1. songs_to_playlist_relation

3. song_to_playlist_relation

The problem is that some kinds of playlists have link to other playlists. But in mysql we don't have foreign key that is associated with two tables.

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

My solution: I will put a third column in songs_to_playlist_relation. That column will be boolean. If 1 then song, else will link to the playlist table.

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

So:

所以：

1. songs_to_playlist_relation

3. song_to_playlist_relation

a) Playlist_number (int) b) Is song (boolean) c) Relative number (song number or playlist number) (int) (notforeign key to any table)

a) Playlist_number (int) b) 是歌曲 (boolean) c) 相对编号（歌曲编号或播放列表编号）（int）（不是任何表的外键）

 #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`"