可能做MySQL外键的两个可能的表之一?

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

现在我添加了一个有两列的popul_areas表;Region_id和popul_place_id。是否有可能使popul_place_id成为国家或州的外键。我可能必须添加一个popul_place_type列，以确定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_nations分别引用州和国家。这些“流行”表中的每一个都引用了用户的个人资料。

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这样的表，它是州和国家的父表。也就是说，州和国家都有一个地方的外键(您甚至可以使这个外键也成为州和国家的主键)。

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

就关系理论而言，多态关联违反了第一范式，因为popul_place_id实际上是一个具有两个含义的列:它要么是一个州，要么是一个国家。您不会将一个人的年龄和电话号码存储在一个列中，出于同样的原因，您也不应该将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)
...

但是这种格式仍然要求您编写对所有表的引用。这是无法回避的。

2009-01-13 22:32:08

其他回答

我意识到这个帖子很旧了，但我看到了这个，我想到了一个解决方案，我想我应该把它扔出去。

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

您可以通过创建一个名为geography _location_type的域表来完全避免这个问题，您可以用三行(Region、Country、State)填充该表。

接下来，创建一个具有外键geography _location_type_id的地理位置表，而不是三个位置表(这样您就知道实例是Region、Country还是State)。

通过使这个表自引用来建模层次结构，这样一个State实例将fKey保存到它的父Country实例，而Country实例又将fKey保存到它的父Region实例。Region实例的fKey值为NULL。这与您使用三个表所做的没有什么不同(您将在地区和国家之间以及国家和州之间有1 -许多关系)，只是现在它们都在一个表中。

popul_user_location表将是user和georgraphical_location之间的范围解析表(因此许多用户可能喜欢许多地方)。

如此如此……

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

不确定目标DB是什么;以上是MS SQL Server。

2019-01-23 06:11:14

这不是世界上最优雅的解决方案，但是您可以使用具体的表继承来实现这一点。

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, places where 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_id would then be a foreign key reference to a row in the places table which would then lead you to a region, country, or state.

您在第二篇专栏文章中提出的描述关联类型的解决方案恰好是Rails如何处理多态关联，但我并不喜欢这种方法。Bill非常详细地解释了为什么多态关联不是你的朋友。

2009-01-13 22:16:37

嗯，我有两张表:

歌曲

a)歌号 b)歌名．...

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

我有第三个

songs_to_playlist_relation

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

我的解决方案:我将在songs_to_playlist_relation中放入第三列。这一列是布尔的。如果1然后歌曲，否则将链接到播放列表表。

So:

songs_to_playlist_relation

a) Playlist_number (int) b) Is song (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`"

2020-05-06 15:32:56

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