database 外键作为主键可以吗？

Is it fine to have foreign key as primary key?

提问by Duc Tran

I have two tables:

我有两个表：

• User (username, password)
• Profile (profileId, gender, dateofbirth, ...)

• 用户（用户名、密码）
• 个人资料（个人资料 ID、性别、出生日期等）

Currently I'm using this approach: each Profile record has a field named "userId" as foreign keywhich links to the User table. When a user registers, his Profile record is automatically created.

目前我正在使用这种方法：每个配置文件记录都有一个名为“userId”的字段作为链接到用户表的外键。当用户注册时，他的个人资料记录会自动创建。

I'm confused with my friend suggestion: to have the "userId" field as the foreignand primarykey and delete the "profileId" field. Which approach is better?

我很困惑与我的朋友建议：有“用户id”字段作为外国和主键和删除“简档”字段。哪种方法更好？

回答by Robert Harvey

Foreign keys are almost always "Allow Duplicates," which would make them unsuitable as Primary Keys.

外键几乎总是“允许重复”，这会使它们不适合作为主键。

Instead, find a field that uniquely identifies each record in the table, or add a new field (either an auto-incrementing integer or a GUID) to act as the primary key.

相反，找到一个唯一标识表中每条记录的字段，或添加一个新字段（自动递增的整数或 GUID）作为主键。

The only exception to this are tables with a one-to-onerelationship, where the foreignkey and primarykey of the linked table are one and the same.

唯一的例外是具有一对一关系的表，其中链接表的外键和主键是一回事。

回答by kotekzot

Primary keys always need to be unique, foreign keys need to allow non-unique values if the table is a one-to-many relationship. It is perfectly fine to use a foreign key as the primary key if the table is connected by a one-to-one relationship, not a one-to-many relationship. If you want the same user record to have the possibility of having more than 1 related profile record, go with a separate primary key, otherwise stick with what you have.

主键必须是唯一的，如果表是一对多关系，外键需要允许非唯一值。如果表是通过一对一关系而不是一对多关系连接的，那么使用外键作为主键是完全可以的。如果您希望同一用户记录有可能有 1 个以上的相关配置文件记录，请使用单独的主键，否则坚持使用您拥有的。

回答by kotekzot

Yes, it is legal to have a primary key being a foreign key. This is a rare construct, but it applies for:

是的，主键是外键是合法的。这是一种罕见的构造，但它适用于：

• a 1:1 relation. The two tables cannot be merged in one because of different permissions and privileges only apply at table level (as of 2017, such a database would be odd).

• a 1:0..1 relation. Profile may or may not exist, depending on the user type.

• performance is an issue, and the design acts as a partition: the profile table is rarely accessed, hosted on a separate disk or has a different sharding policy as compared to the users table. Would not make sense if the underlining storage is columnar.

• 1:1 的关系。这两个表不能合并为一个，因为不同的权限和特权仅适用于表级别（截至 2017 年，这样的数据库会很奇怪）。

• 1:0..1 的关系。配置文件可能存在也可能不存在，具体取决于用户类型。

• 性能是一个问题，设计就像一个分区：与用户表相比，配置文件表很少被访问，托管在单独的磁盘上或具有不同的分片策略。如果下划线存储是柱状的，则没有意义。

回答by Tshsmith

It is generally considered bad practise to have a one to one relationship. This is because you could just have the data represented in one table and achieve the same result.

拥有一对一的关系通常被认为是不好的做法。这是因为您可以只在一张表中表示数据并获得相同的结果。

However, there are instances where you may not be able to make these changes to the table you are referencing. In this instance there is no problem using the Foreign key as the primary key. It might help to have a composite key consisting of an auto incrementing unique primary key and the foreign key.

但是，在某些情况下，您可能无法对所引用的表进行这些更改。在这种情况下，使用外键作为主键没有问题。拥有一个由自动递增的唯一主键和外键组成的复合键可能会有所帮助。

I am currently working on a system where users can log in and generate a registration code to use with an app. For reasons I won't go into I am unable to simply add the columns required to the users table. So I am going down a one to one route with the codes table.

我目前正在开发一个系统，用户可以在其中登录并生成注册码以与应用程序一起使用。由于我不会讨论的原因，我无法简单地将所需的列添加到用户表中。因此，我将使用代码表进行一对一的路由。

回答by Riaj Ferdous

Yes, a foreign key can be a primary key in the case of one to one relationship between those tables

是的，在这些表之间存在一对一关系的情况下，外键可以是主键

回答by Massimiliano Peluso

I would not do that. I would keep the profileIDas primary key of the table Profile

我不会那样做。我会保留profileID表的主键Profile

A foreign key is just a referential constraint between two tables

外键只是两个表之间的引用约束

One could argue that a primary key is necessary as the target of any foreign keys which refer to it from other tables. A foreign key is a set of one or more columns in any table (not necessarily a candidate key, let alone the primary key, of that table) which may hold the value(s) found in the primary key column(s) of some other table. So we must have a primary key to match the foreign key. Or must we? The only purpose of the primary key in the primary key/foreign key pair is to provide an unambiguous join - to maintain referential integrity with respect to the "foreign" table which holds the referenced primary key. This insures that the value to which the foreign key refers will always be valid (or null, if allowed).

有人可能会争辩说，作为从其他表引用它的任何外键的目标，主键是必要的。外键是任何表中的一组一个或多个列（不一定是该表的候选键，更不用说主键了），它可以保存在某些表的主键列中找到的值其他表。所以我们必须有一个主键来匹配外键。还是我们必须？主键/外键对中主键的唯一目的是提供明确的连接 - 保持与保存被引用主键的“外”表相关的参照完整性。这确保外键引用的值始终有效（如果允许，则为 null）。

http://www.aisintl.com/case/primary_and_foreign_key.html

http://www.aisintl.com/case/primary_and_foreign_key.html

回答by Vincent Cai

It depends on the business and system.

这取决于业务和系统。

If your userId is unique and will be unique all the time, you can use userId as your primary key. But if you ever want to expand your system, it will make things difficult. I advise you to add a foreign key in table user to make a relationship with table profile instead of adding a foreign key in table profile.

如果您的 userId 是唯一的并且一直都是唯一的，您可以使用 userId 作为您的主键。但是如果你想扩展你的系统，事情就会变得很困难。我建议您在表用户中添加外键以与表配置文件建立关系，而不是在表配置文件中添加外键。

回答by hfontanez

Short answer: DEPENDS.... In this particular case, it might be fine. However, experts will recommend against it just about every time; including your case.

简短回答：取决于......在这种特殊情况下，可能没问题。但是，专家几乎每次都会建议不要这样做。包括你的情况。

Why?

为什么？

Keys are seldomly unique in tables when they are foreign (originated in another table) to the table in question. For example, an item ID might be unique in an ITEMS table, but not in an ORDERS table, since the same type of item will most likely exist in another order. Likewise, order IDs might be unique (might) in the ORDERS table, but not in some other table like ORDER_DETAILS where an order with multiple line items can exist and to query against a particular item in a particular order, you need the concatenation of two FK (order_id and item_id) as the PK for this table.

当表中的键是外来的（源自另一个表）时，表中的键很少是唯一的。例如，项目 ID 在 ITEMS 表中可能是唯一的，但在 ORDERS 表中不是，因为相同类型的项目很可能存在于另一个订单中。同样，订单 ID 在 ORDERS 表中可能是唯一的（可能），但在 ORDER_DETAILS 等其他表中可能是唯一的，其中可以存在具有多个行项目的订单，并且要查询特定订单中的特定项目，您需要串联两个FK（order_id 和 item_id）作为该表的 PK。

I am not DB expert, but if you can justify logically to have an auto-generated value as your PK, I would do that. If this is not practical, then a concatenation of two (or maybe more) FK could serve as your PK. BUT, I cannot think of any case where a single FK value can be justified as the PK.

我不是数据库专家，但如果您能从逻辑上证明将自动生成的值作为您的 PK，我会这样做。如果这不切实际，那么两个（或更多）FK 的串联可以作为您的 PK。但是，我想不出任何可以将单个 FK 值证明为 PK 的情况。