I'm creating a database table and I don't have a logical primary key assigned to it. So, I'm thinking about leaving it without a primary key, but I'm felling a bit guiltly about it. Should I?
我正在创建一个数据库表，而我没有分配给它的逻辑主键。所以，我想把它放在没有主键的地方，但我觉得有点内疚。我应该吗?
Should each and every table have a primary key?
是否每个表都有一个主键?
EDIT: Okay, okay... I've created the primary key! Are you happy now? :)
编辑:好吧,好吧…我已经创建了主键!你现在快乐吗?:)
13 个解决方案
#1
251
Short answer: yes.
简短的回答:是的。
Long answer:
长一点的回答:
- You need your table to be joinable on something
- 你需要你的桌子在某些东西上是可接合的。
- If you want your table to be clustered, you need some kind of a primary key.
- 如果希望将表集群化，则需要某种主键。
- If your table design does not need a primary key, rethink your design: most probably, you are missing something. Why keep identical records?
- 如果您的表设计不需要主键，那么重新考虑您的设计:最可能的是，您缺少了一些东西。为什么要保存相同的记录?
In MySQL, the InnoDB storage engine always creates a primary key if you didn't specify it explicitly, thus making an extra column you don't have access to.
在MySQL中，InnoDB存储引擎总是创建一个主键，如果您没有显式地指定它，那么就会生成一个无法访问的额外列。
Note that a primary key can be composite.
注意，主键可以是复合的。
If you have a many-to-many link table, you create the primary key on all fields involved in the link. Thus you ensure that you don't have two or more records describing one link.
如果您有一个多对多的链接表，那么您将在链接的所有字段中创建主键。因此，您可以确保没有两个或更多的记录来描述一个链接。
Besides the logical consistency issues, most RDBMS engines will benefit from including these fields in a unique index.
除了逻辑一致性问题之外，大多数RDBMS引擎将从包含这些字段的唯一索引中受益。
And since any primary key involves creating a unique index, you should declare it and get both logical consistency and performance.
由于任何主键都涉及创建一个惟一的索引，所以您应该声明它并获得逻辑一致性和性能。
See this article in my blog for why you should always create a unique index on unique data:
请在我的博客中看到这篇文章，为什么你应该总是在唯一的数据上创建一个独特的索引:
- Making an index UNIQUE
- 做一个索引独特
P.S. There are some very, very special cases where you don't need a primary key.
附注:有一些非常非常特殊的情况，你不需要主键。
Mostly they include log tables which don't have any indexes for performance reasons.
主要是由于性能原因，日志表没有任何索引。
#2
26
Always best to have a primary key. This way it meets first normal form and allows you to continue along the database normalization path.
总是最好有一个主键。这样，它满足了第一范式，并允许您沿着数据库规范化路径继续前进。
As stated by others, there are some reasons not to have a primary key, but most will not be harmed if there is a primary key
正如其他人所指出的，没有主键是有原因的，但是如果有主键，大多数都不会受到伤害。
#3
10
Pretty much any time I've created a table without a primary key, thinking I wouldn't need one, I've ended up going back and adding one. I now create even my join tables with an auto-generated identity field that I use as the primary key.
几乎任何时候，我创建了一个没有主键的表，认为我不需要一个键，我已经返回并添加了一个。现在，我甚至创建了一个自动生成的标识字段的连接表，作为主键。
#4
9
Except for a few very rare cases (possibly a many-to-many relationship table, or a table you temporarily use for bulk-loading huge amounts of data), I would go with the saying:
除了一些非常罕见的情况(可能是多对多关系表，或者临时用于批量加载大量数据的表)，我还会说:
If it doesn't have a primary key, it's not a table!
如果它没有主键，它就不是一个表!
Marc
马克
#5
6
Just add it, you will be sorry later when you didn't (selecting, deleting. linking, etc)
只需添加它，当您没有(选择、删除)时，您就会后悔。连接等)
#6
6
Will you ever need to join this table to other tables? Do you need a way to uniquely identify a record? If the answer is yes, you need a primary key. Assume your data is something like a customer table that has the names of the people who are customers. There may be no natural key because you need the addresses, emails, phone numbers, etc. to determine if this Sally Smith is different from that Sally Smith and you will be storing that information in related tables as the person can have mulitple phones, addesses, emails, etc. Suppose Sally Smith marries John Jones and becomes Sally Jones. If you don't have an artifical key onthe table, when you update the name, you just changed 7 Sally Smiths to Sally Jones even though only one of them got married and changed her name. And of course in this case withouth an artificial key how do you know which Sally Smith lives in Chicago and which one lives in LA?
您是否需要将此表连接到其他表?您是否需要一种方法来唯一地标识一个记录?如果答案是肯定的，你需要一个主键。假设您的数据类似于客户表，其中有客户的名称。可能没有自然的关键,因为你需要地址,电子邮件,电话号码,等来确定这个史密斯与莎莉莎莉史密斯是不同,你会将这些信息存储在相关表的人可以有mulitple手机,addesses,电子邮件,等等。假设莎莉史密斯嫁给约翰·琼斯和莎莉。琼斯。如果你在桌子上没有一个人工钥匙，当你更新这个名字的时候，你只是把7个莎莉·史密斯换成了莎莉·琼斯，尽管他们中只有一个结了婚并改了名字。当然，在这种情况下，用人工钥匙你怎么知道莎莉·史密斯住在芝加哥哪一个住在洛杉矶?
You say you have no natural key, therefore you don't have any combinations of field to make unique either, this makes the artficial key critical.
你说你没有天然的钥匙，所以你没有任何的能量场的组合来制造独特的，这使艺术的关键关键。
I have found anytime I don't have a natural key, an artifical key is an absolute must for maintaining data integrity. If you do have a natural key, you can use that as the key field instead. But personally unless the natural key is one field, I still prefer an artifical key and unique index on the natural key. You will regret it later if you don't put one in.
我发现，任何时候我都没有一个自然的钥匙，一个人工的钥匙是保持数据完整性的绝对必需。如果您有一个自然的键，您可以使用它作为键字段。但就个人而言，除非天然钥匙是一个领域，我还是更喜欢自然钥匙上的人工键和独特的索引。如果你不放进去，你以后会后悔的。
#7
4
It is a good practice to have a PK on every table, but it's not a MUST. Most probably you will need a unique index, and/or a clustered index (which is PK or not) depending on your need.
在每个表上都有一个PK是很好的做法，但这不是必须的。大多数情况下，您可能需要一个惟一的索引和/或一个聚集索引(根据您的需要而不是PK)。
Check out the Primary Keys and Clustered Indexes sections on Books Online (for SQL Server)
在联机丛书(对于SQL Server)上检查主键和聚集索引节
"PRIMARY KEY constraints identify the column or set of columns that have values that uniquely identify a row in a table. No two rows in a table can have the same primary key value. You cannot enter NULL for any column in a primary key. We recommend using a small, integer column as a primary key. Each table should have a primary key. A column or combination of columns that qualify as a primary key value is referred to as a candidate key."
主键约束标识具有惟一标识表中一行的值的列或列。表中没有两行可以具有相同的主键值。对于主键中的任何列，都不能输入NULL。我们建议使用一个小的整数列作为主键。每个表都应该有一个主键。作为一个主键值的列的列或组合被称为候选键。
But then check this out also: http://www.aisintl.com/case/primary_and_foreign_key.html
然后再检查一下:http://www.aisintl.com/case/primary_and_foreign_key.html。
#8
3
I know that in order to use certain features of the gridview in .NET, you need a primary key in order for the gridview to know which row needs updating/deleting. General practice should be to have a primary key or primary key cluster. I personally prefer the former.
我知道为了在。net中使用gridview的某些特性，您需要一个主键，以便gridview知道哪些行需要更新/删除。一般的做法应该是有一个主键或主键集群。我个人更喜欢前者。
#9
2
I always have a primary key, even if in the beginning I don't have a purpose in mind yet for it. There have been a few times when I eventually need a PK in a table that doesn't have one and it's always more trouble to put it in later. I think there is more of an upside to always including one.
我总是有一个主键，即使在开始的时候我还没有目标。有几次，我最终需要在一个没有一个的表中进行PK，以后再把它放到后面总是麻烦不断。我认为总是有更多的好处，包括一个。
#10
2
To make it future proof you really should. If you want to replicate it you'll need one. If you want to join it to another table your life (and that of the poor fools who have to maintain it next year) will be so much easier.
为了让它成为未来的证明，你真的应该。如果你想复制它，你需要一个。如果你想要加入到另一张桌子上，你的生活(以及那些不得不在明年保持它的可怜的傻瓜们)将会变得容易得多。
#11
1
In short, no. However, you need to keep in mind that certain client access CRUD operations require it. For future proofing, I tend to always utilize primary keys.
简而言之,没有。但是，您需要记住，某些客户端访问CRUD操作需要它。为了以后的打样，我倾向于总是使用主键。
#12
1
If you are using Hibernate its not possible to create an Entity without a primary key. This issues can create problem if you are working with an existing database which was created with plain sql/ddl scripts, and no primary key was added
如果使用Hibernate，则不可能创建没有主键的实体。如果您使用的是使用纯sql/ddl脚本创建的现有数据库，并且没有添加主键，那么这个问题就会产生问题。
#13
1
I am in the role of maintaining application created by offshore development team. Now I am having all kinds of issues in the application because original database schema did not contain PRIMARY KEYS on some tables. So please dont let other people suffer because of your poor design. It is always good idea to have primary keys on tables.
我正在维护由离岸开发团队创建的应用程序。现在我在应用程序中有各种各样的问题，因为原始数据库模式不包含一些表上的主键。所以请不要因为你的设计太差而让别人受苦。在表上拥有主键始终是个好主意。
#1
251
Short answer: yes.
简短的回答:是的。
Long answer:
长一点的回答:
- You need your table to be joinable on something
- 你需要你的桌子在某些东西上是可接合的。
- If you want your table to be clustered, you need some kind of a primary key.
- 如果希望将表集群化，则需要某种主键。
- If your table design does not need a primary key, rethink your design: most probably, you are missing something. Why keep identical records?
- 如果您的表设计不需要主键，那么重新考虑您的设计:最可能的是，您缺少了一些东西。为什么要保存相同的记录?
In MySQL, the InnoDB storage engine always creates a primary key if you didn't specify it explicitly, thus making an extra column you don't have access to.
在MySQL中，InnoDB存储引擎总是创建一个主键，如果您没有显式地指定它，那么就会生成一个无法访问的额外列。
Note that a primary key can be composite.
注意，主键可以是复合的。
If you have a many-to-many link table, you create the primary key on all fields involved in the link. Thus you ensure that you don't have two or more records describing one link.
如果您有一个多对多的链接表，那么您将在链接的所有字段中创建主键。因此，您可以确保没有两个或更多的记录来描述一个链接。
Besides the logical consistency issues, most RDBMS engines will benefit from including these fields in a unique index.
除了逻辑一致性问题之外，大多数RDBMS引擎将从包含这些字段的唯一索引中受益。
And since any primary key involves creating a unique index, you should declare it and get both logical consistency and performance.
由于任何主键都涉及创建一个惟一的索引，所以您应该声明它并获得逻辑一致性和性能。
See this article in my blog for why you should always create a unique index on unique data:
请在我的博客中看到这篇文章，为什么你应该总是在唯一的数据上创建一个独特的索引:
- Making an index UNIQUE
- 做一个索引独特
P.S. There are some very, very special cases where you don't need a primary key.
附注:有一些非常非常特殊的情况，你不需要主键。
Mostly they include log tables which don't have any indexes for performance reasons.
主要是由于性能原因，日志表没有任何索引。
#2
26
Always best to have a primary key. This way it meets first normal form and allows you to continue along the database normalization path.
总是最好有一个主键。这样，它满足了第一范式，并允许您沿着数据库规范化路径继续前进。
As stated by others, there are some reasons not to have a primary key, but most will not be harmed if there is a primary key
正如其他人所指出的，没有主键是有原因的，但是如果有主键，大多数都不会受到伤害。
#3
10
Pretty much any time I've created a table without a primary key, thinking I wouldn't need one, I've ended up going back and adding one. I now create even my join tables with an auto-generated identity field that I use as the primary key.
几乎任何时候，我创建了一个没有主键的表，认为我不需要一个键，我已经返回并添加了一个。现在，我甚至创建了一个自动生成的标识字段的连接表，作为主键。
#4
9
Except for a few very rare cases (possibly a many-to-many relationship table, or a table you temporarily use for bulk-loading huge amounts of data), I would go with the saying:
除了一些非常罕见的情况(可能是多对多关系表，或者临时用于批量加载大量数据的表)，我还会说:
If it doesn't have a primary key, it's not a table!
如果它没有主键，它就不是一个表!
Marc
马克
#5
6
Just add it, you will be sorry later when you didn't (selecting, deleting. linking, etc)
只需添加它，当您没有(选择、删除)时，您就会后悔。连接等)
#6
6
Will you ever need to join this table to other tables? Do you need a way to uniquely identify a record? If the answer is yes, you need a primary key. Assume your data is something like a customer table that has the names of the people who are customers. There may be no natural key because you need the addresses, emails, phone numbers, etc. to determine if this Sally Smith is different from that Sally Smith and you will be storing that information in related tables as the person can have mulitple phones, addesses, emails, etc. Suppose Sally Smith marries John Jones and becomes Sally Jones. If you don't have an artifical key onthe table, when you update the name, you just changed 7 Sally Smiths to Sally Jones even though only one of them got married and changed her name. And of course in this case withouth an artificial key how do you know which Sally Smith lives in Chicago and which one lives in LA?
您是否需要将此表连接到其他表?您是否需要一种方法来唯一地标识一个记录?如果答案是肯定的，你需要一个主键。假设您的数据类似于客户表，其中有客户的名称。可能没有自然的关键,因为你需要地址,电子邮件,电话号码,等来确定这个史密斯与莎莉莎莉史密斯是不同,你会将这些信息存储在相关表的人可以有mulitple手机,addesses,电子邮件,等等。假设莎莉史密斯嫁给约翰·琼斯和莎莉。琼斯。如果你在桌子上没有一个人工钥匙，当你更新这个名字的时候，你只是把7个莎莉·史密斯换成了莎莉·琼斯，尽管他们中只有一个结了婚并改了名字。当然，在这种情况下，用人工钥匙你怎么知道莎莉·史密斯住在芝加哥哪一个住在洛杉矶?
You say you have no natural key, therefore you don't have any combinations of field to make unique either, this makes the artficial key critical.
你说你没有天然的钥匙，所以你没有任何的能量场的组合来制造独特的，这使艺术的关键关键。
I have found anytime I don't have a natural key, an artifical key is an absolute must for maintaining data integrity. If you do have a natural key, you can use that as the key field instead. But personally unless the natural key is one field, I still prefer an artifical key and unique index on the natural key. You will regret it later if you don't put one in.
我发现，任何时候我都没有一个自然的钥匙，一个人工的钥匙是保持数据完整性的绝对必需。如果您有一个自然的键，您可以使用它作为键字段。但就个人而言，除非天然钥匙是一个领域，我还是更喜欢自然钥匙上的人工键和独特的索引。如果你不放进去，你以后会后悔的。
#7
4
It is a good practice to have a PK on every table, but it's not a MUST. Most probably you will need a unique index, and/or a clustered index (which is PK or not) depending on your need.
在每个表上都有一个PK是很好的做法，但这不是必须的。大多数情况下，您可能需要一个惟一的索引和/或一个聚集索引(根据您的需要而不是PK)。
Check out the Primary Keys and Clustered Indexes sections on Books Online (for SQL Server)
在联机丛书(对于SQL Server)上检查主键和聚集索引节
"PRIMARY KEY constraints identify the column or set of columns that have values that uniquely identify a row in a table. No two rows in a table can have the same primary key value. You cannot enter NULL for any column in a primary key. We recommend using a small, integer column as a primary key. Each table should have a primary key. A column or combination of columns that qualify as a primary key value is referred to as a candidate key."
主键约束标识具有惟一标识表中一行的值的列或列。表中没有两行可以具有相同的主键值。对于主键中的任何列，都不能输入NULL。我们建议使用一个小的整数列作为主键。每个表都应该有一个主键。作为一个主键值的列的列或组合被称为候选键。
But then check this out also: http://www.aisintl.com/case/primary_and_foreign_key.html
然后再检查一下:http://www.aisintl.com/case/primary_and_foreign_key.html。
#8
3
I know that in order to use certain features of the gridview in .NET, you need a primary key in order for the gridview to know which row needs updating/deleting. General practice should be to have a primary key or primary key cluster. I personally prefer the former.
我知道为了在。net中使用gridview的某些特性，您需要一个主键，以便gridview知道哪些行需要更新/删除。一般的做法应该是有一个主键或主键集群。我个人更喜欢前者。
#9
2
I always have a primary key, even if in the beginning I don't have a purpose in mind yet for it. There have been a few times when I eventually need a PK in a table that doesn't have one and it's always more trouble to put it in later. I think there is more of an upside to always including one.
我总是有一个主键，即使在开始的时候我还没有目标。有几次，我最终需要在一个没有一个的表中进行PK，以后再把它放到后面总是麻烦不断。我认为总是有更多的好处，包括一个。
#10
2
To make it future proof you really should. If you want to replicate it you'll need one. If you want to join it to another table your life (and that of the poor fools who have to maintain it next year) will be so much easier.
为了让它成为未来的证明，你真的应该。如果你想复制它，你需要一个。如果你想要加入到另一张桌子上，你的生活(以及那些不得不在明年保持它的可怜的傻瓜们)将会变得容易得多。
#11
1
In short, no. However, you need to keep in mind that certain client access CRUD operations require it. For future proofing, I tend to always utilize primary keys.
简而言之,没有。但是，您需要记住，某些客户端访问CRUD操作需要它。为了以后的打样，我倾向于总是使用主键。
#12
1
If you are using Hibernate its not possible to create an Entity without a primary key. This issues can create problem if you are working with an existing database which was created with plain sql/ddl scripts, and no primary key was added
如果使用Hibernate，则不可能创建没有主键的实体。如果您使用的是使用纯sql/ddl脚本创建的现有数据库，并且没有添加主键，那么这个问题就会产生问题。
#13
1
I am in the role of maintaining application created by offshore development team. Now I am having all kinds of issues in the application because original database schema did not contain PRIMARY KEYS on some tables. So please dont let other people suffer because of your poor design. It is always good idea to have primary keys on tables.
我正在维护由离岸开发团队创建的应用程序。现在我在应用程序中有各种各样的问题，因为原始数据库模式不包含一些表上的主键。所以请不要因为你的设计太差而让别人受苦。在表上拥有主键始终是个好主意。