I have table "User" (username, password) and table "Profile" (profileId, gender, dateofbirth,...). Currently I'm using this approach: each Profile record has a field named "userId" as foreign key which links to the User table. When an user registers, his Profile record is automatically created. I'm confused with my friend suggestion: to have the "userId" field as the foreign and primary key and delete the "profileId" field. Which approach is better ?
我有表“用户”(用户名、密码)和表“配置文件”(profileId、gender、dateofbirth，…)。目前我正在使用这种方法:每个配置文件记录都有一个名为“userId”的字段作为外键，它链接到用户表。当用户注册时，将自动创建他的概要记录。我不同意我朋友的建议:将“userId”字段作为外键和主键，并删除“profileId”字段。哪种方法更好?
7 个解决方案
#1
75
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-one relationship, where the foreign key and primary key of the linked table are one and the same.
唯一的例外是具有一对一关系的表，其中链接表的外键和主键是相同的。
#2
22
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个相关的概要记录，请使用单独的主键，否则请继续使用您所拥有的。
#3
4
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.
我目前正在开发一个系统，在这个系统中，用户可以登录并生成一个注册码，以便与应用程序一起使用。所以我要用代码表，沿着一条路径走下去。
#4
2
Yes, a foreign key can be a primary key in the case of one to one relationship between those tables
是的，在这些表之间的一对一关系中，外键可以是主键
#5
1
I would not do that. I would keep the profileID as primary key of the table Profile
我不会那样做。我将把profileID作为表概要文件的主键
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
#6
1
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作为您的主键。但是如果你想扩展你的系统，它会使事情变得困难。我建议您在表用户中添加外键，以与表配置文件建立关系，而不是在表配置文件中添加外键。
#7
1
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).
1:1的关系。这两个表不能合并到一个表中，因为不同的权限和权限只适用于表级别(到2017年，这样的数据库将是奇怪的)。
-
a 1:0..1 relation. Profile may or may not exist, depending on the user type.
一个1:0 . .1的关系。概要文件可能存在，也可能不存在，这取决于用户类型。
-
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.
性能是一个问题，设计作为一个分区:配置文件表很少被访问，托管在一个单独的磁盘上，或者与users表相比具有不同的分片策略。如果下面的存储是柱状的，则没有意义。
#1
75
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-one relationship, where the foreign key and primary key of the linked table are one and the same.
唯一的例外是具有一对一关系的表，其中链接表的外键和主键是相同的。
#2
22
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个相关的概要记录，请使用单独的主键，否则请继续使用您所拥有的。
#3
4
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.
我目前正在开发一个系统，在这个系统中，用户可以登录并生成一个注册码，以便与应用程序一起使用。所以我要用代码表，沿着一条路径走下去。
#4
2
Yes, a foreign key can be a primary key in the case of one to one relationship between those tables
是的，在这些表之间的一对一关系中，外键可以是主键
#5
1
I would not do that. I would keep the profileID as primary key of the table Profile
我不会那样做。我将把profileID作为表概要文件的主键
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
#6
1
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作为您的主键。但是如果你想扩展你的系统，它会使事情变得困难。我建议您在表用户中添加外键，以与表配置文件建立关系，而不是在表配置文件中添加外键。
#7
1
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).
1:1的关系。这两个表不能合并到一个表中，因为不同的权限和权限只适用于表级别(到2017年，这样的数据库将是奇怪的)。
-
a 1:0..1 relation. Profile may or may not exist, depending on the user type.
一个1:0 . .1的关系。概要文件可能存在，也可能不存在，这取决于用户类型。
-
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.
性能是一个问题，设计作为一个分区:配置文件表很少被访问，托管在一个单独的磁盘上，或者与users表相比具有不同的分片策略。如果下面的存储是柱状的，则没有意义。