复合主键 [英] Composite primary key

问题描述

我正在设计一个数据库，用于存储来自多个不同来源的数据。我存储的实例由原始来源分配唯一的ID。每个实例我存储应该包含它来源的信息，以及该源所关联的ID。

例如，问题：

  ----------------------- ----------------------------------------- 
 | source_id | id_on_source |数据| 
 ---------------------------------------------- ------------------ 
 | 1 | 17600 | ... | 
 | 1 | 17601 | ... | 
 | 2 | 1 | ... | 
 | 3 | 1 | ... | 
 ---------------------------------------------- ------------------ 
  

请注意，虽然 id_on_source 对于每个源是唯一的，可以为不同的源找到相同的 id_on_source 。

我对关系数据库有一个正确的理解，但是远不是一个专家，甚至是一个有经验的用户。我面对这个设计的问题是我应该使用作为主键。数据似乎决定了使用（source_id，id_on_source）的复合主键。经过一点谷歌搜索后，我发现一些激烈的辩论关于复合主键的利弊，但让我有点困惑。

表将与其他表具有一对多关系，因此将在其他表的外键中引用。

我没有绑定到一个特定的 RDBMS ，我不知道这是否为了争论的重要性，但让我们说使用 SQLite 和 MySQL 。

在这种情况下使用复合外键的利弊？

解决方案

我个人认为复合主键是痛苦的。对于您希望加入到来源表中的每个表，您都需要添加source_id和id_on_source字段。

我将创建一个标准的自动递增主表键，然后在source_id和id_on_source列上添加一个唯一索引。

这样就可以只将源表的id添加为其他表格。

一般来说，我还发现在许多框架和工具产品中对复合主键的支持是最好的补丁，而在其他框架中不存在。

I am working on the design of a database that will be used to store data that originates from a number of different sources. The instances I am storing are assigned unique IDs by the original sources. Each instance I store should contain information about the source it came from, along with the ID it was associated by this source.

As an example, consider the following table that illustrates the problem:

----------------------------------------------------------------
| source_id | id_on_source | data                              |
----------------------------------------------------------------
| 1         | 17600        | ...                               |
| 1         | 17601        | ...                               |
| 2         | 1            | ...                               |
| 3         | 1            | ...                               |
----------------------------------------------------------------

Note that while the id_on_source is unique for each source, it is possible for the same id_on_source to be found for different sources.

I have a decent understanding of relational databases, but am far from an expert or even an experienced user. The problem I face with this design is what I should use as primary key. The data seems to dictate the use of a composite primary key of (source_id, id_on_source). After a little googling I found some heated debates on the pros and cons of composite primary keys however, leaving me a little confused.

The table will have one-to-many relationship with other tables, and will thus be referred to in the foreign keys of other tables.

I am not tied to a specific RDBMS and I am not sure if it matters for the sake of the argument, but let's say that I prefer to work with SQLite and MySQL.

What are the pros and cons of using a composite foreign key in this case? Which would you prefer?

解决方案

I personally find composite primary keys to be painful. For every table that you wish to join to your "sources" table you will need to add both the source_id and id_on_source field.

I would create a standard auto-incrementing primary key on your sources table and add a unique index on source_id and id_on_source columns.

This then allows you to add just the id of the sources table as a foreign key on other tables.

Generally I have also found support for composite primary keys within many frameworks and tooling products to be "patchy" at best and non-existent in others

这篇关于复合主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！