SQL 主键 - 有必要吗？
我有一份物品清单。这些商品中的大多数都没有库存。项目表有 id、名称、描述。物品的数量存储在另一个名为 inventory 的表中。库存表包含 item_id 和库存商品数量。
库存表需要主键吗？如果是这样，我应该使用序列密钥还是复合密钥？表什么时候可以没有主键？
编辑：谢谢大家提供的信息。除了极少数例外情况外，我现在将始终拥有主键。我还了解了更多关于串行键与复合键的知识。
I have a list of items. Most of these items will not be in stock. The item table has id, name, description. The quantities of items are stored in another table named inventory. The inventory table has item_id and quantity of items that are in stock.
Do I need a primary key for the inventory table? If so, should I use a serial or composite key? When is it ok for a table to not have a primary key?
Edit: Thank you all for being very informative. I will now always have primary keys except in very rare exceptions. I also learned a bit more about serial vs composite keys.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
始终以拥有主键为目标。
如果您不确定，请拥有主键。
即使您 99.99% 确定不需要它，也应该拥有一个。随着我多年经验的积累，需求发生了变化。
我真正能想到的唯一例子是只有两个外键的多对多表和每个字节都很重要的巨型（数亿行）表。但即便如此，仍然强烈建议使用单独的、唯一的、无商业价值的 id 密钥。
这里有一些关于此的更多重要信息：
http://weblogs.sqlteam.com/jeffs/archive/2007 /08/23/composite_primary_keys.aspx
和此处：
http://www.techrepublic.com/article/the-great -primary-key-debate/1045050
这里：
http://databases.aspfaq.com /database/what-should-i-choose-for-my-primary-key.html
在这里：
我是否应该使用复合主键？
在您的示例中，我肯定会拥有一个。
“不”拥有一个的决定应该基于非常明确的需求和理解以及拥有一个的实际或预测（例如数量）问题。
调试和故障排除时出现了这种需求的一个很好的例子。就像在每个表中创建和更新列一样（我的另一个最爱），此信息最初可能不会被前端使用/用于前端，但它对跟踪和解决问题有帮助吗？ （顺便说一句，更新标记现在通常是 Ruby On Rails 等框架的标准配置，它也可以很好地适应每个具有
id字段的表的约定！）Always aim to have a primary key.
If you are unsure, have a primary key.
Even if you are 99.99% sure you will not need it, have one. Requirements change as I have learned through experience over many years.
The only examples I can really think of are many-to-many tables with just two foreign_keys and mega-huge (hundreds of millions of rows) tables where every byte counts. But even then a separate, unique, no-business value id key is still strongly recommended.
There's some more great info on this here:
http://weblogs.sqlteam.com/jeffs/archive/2007/08/23/composite_primary_keys.aspx
and here:
http://www.techrepublic.com/article/the-great-primary-key-debate/1045050
here:
http://databases.aspfaq.com/database/what-should-i-choose-for-my-primary-key.html
and here:
Should I use composite primary keys or not?
In your example, I would definitely have one.
The decision to 'not' have one should be based on a very clear need and understanding and actual or predicted (e.g. volume) issues with having one.
One great example of this need comes up when debugging and troubleshooting. Just like having create and update columns in each table (another favorite of mine), this info may not initially be used by/for the front end but boy can it be helpful in tracing and resolving issues. (btw update stamps are often now standard in frameworks like Ruby On Rails which also works well with the convention of every table having an
idfield!)我们可以假设数据是相关的吗？根据定义，关系没有重复的元组。 SQL 允许表中存在重复行。因此，为了确保实际中不存在重复行，每个表都应该至少有一个唯一约束。长话短说，您最好有充分的理由不对表中的每个候选键设置唯一约束。根据定义，零个或一个候选密钥可以被指定为“主”，并且哪一个（如果有）应该接收这一指定是任意的。
我认为这是一个错字。单列密钥称为“简单密钥”，而不是“串行密钥”。根据您的描述，您的 Inventory 表在
item_ID 上有一个唯一的候选键，这是一个简单的键。唯一可能的复合键是超级键，除非它由外键引用，否则不应使用唯一约束进行约束。当所有候选键都已使用
UNIQUE约束进行约束或表不打算保存关系数据时。Can we assume the data is relational? A relation has no duplicate tuples by definition. SQL allows duplicate rows in a table. Therefore, to ensure no duplicate rows in practice, every table should have at least one unique constraint. To cut a long story short, you better have a good reason for not placing a unique constraint on every candidate key in the table. By definition, zero or one candidate key may be designated 'primary' and which one (if any) should receive this designation is arbitrary.
I think this is a typo. A single-column key is known as a "simple key" and not "serial key". From your description, your Inventory table has a sole candidate candidate key on
item_ID which is a simple key. The only possible composite key is a superkey and, unless it is to be referenced by a foreign key, should not be constrained using a unique constraint.When all candidate keys have been constrained using
UNIQUEconstraints or when the table is not intended to hold relational data.总的来说：每桌都应该有一个PK。至少每个表都应该有一些 CLUSTER 索引。
PK 不能是一个特殊列，但在系统 (RDBMS) 中包含没有唯一标识的行并不是一个好的做法。
可能有几种情况不需要 PK，但这是规则中的例外情况。
In general: Every table should have a PK. At least every table should have some CLUSTER index.
The PK must not be one special column, but having rows without unique identification in system (RDBMS) is not good practise.
There can be several cases where PK is not required, but that are exceptions in rule.
如果 item_id 在库存表中是唯一的，我想说你可以使用它作为标识符。主键通常用于唯一标识一条线路，但在我看来，在您的情况下，库存线路标识没有用处。
编辑：正如其他人所指出的，通常如果您没有充分的理由使用主键，您将很好地查看您的表结构，看看是否可以将其与另一个表合并，在这种情况下可能是项目桌子。我可以看到有些情况下这不是一个选项（例如，您无法更改架构，只需添加新表），但值得一看。
If item_id is unique in the inventory table, I'd say you're fine with using that as an identifier. A primary key is usually used to uniquely identify a line, but there is no use for an inventory line identity in your case that I can see.
EDIT: As others have noted, generally if you don't have a good reason for a primary key, you'll be well off looking at your table structure to see if you can merge it with another table, in this case probably the items table. I can see cases where that's not an option (for example that you can't change the schema, just add new tables) but it's worth a look.
如果每个项目只有一个库存行 - 那么它会便宜得多（意味着 - CPU 和 IO），它将位于同一个项目表中，
如果没有 - 这取决于。它根本不会是标准化数据
但据我了解这个问题，如果你坚持两个表 - 是的，最好在 item_id 字段上有一个索引
If you have only one inventory row per item - then it will be much cheaper (mean - CPU and IO) that it will be in the same Item table,
if not - it depends. And it will not be a normalized data at all
But as far as I understand the question and if you insist on two tables - yes, its better to have an index on item_id field