主键是什么？
我有一个关于数据库设计的问题。 我有两个表，usertable 和historytable，它们是一对多关系，其中usertable 包含
- 用户名主键
- passwordname
和historytable 包含
- 用户名外键
- datevisted
- url
我不确定如果在性能方面是否有任何负面影响历史表没有定义主键，如果有，历史表中主键的最佳选择是什么？
谢谢。
I have a question about Database design.
I have two tables, usertable and historytable, which are one-to-many relationship, which usertable contains
- username primary key
- passwordname
and historytable has
- username foreign key
- date
- visted url
I am not sure if there are any negative effects in terms of performance if historytable has no primary key defined , if there are , what would be the best option in the historytable to be the primary key?
Thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
您需要定义一个新列：UserHistoryId
使其成为标识列
这样做的原因是其他列的组合在所有情况下都不可能是唯一的。
例如，如果同一 url 的用户在一天内创建两次历史记录。
性能：
取决于历史表的使用方式 - 如果您仅通过对 userId、日期或 Url 进行查询来从中选择数据，则 ID 列将毫无用处。
但是，如果您对表执行任何更新/删除操作，则 Id 将会很有用。
无论当前的要求如何，现在添加额外的列几乎不需要花费任何成本，而且我总是推荐这样做。
You would need to define a new column: UserHistoryId
Make it identity column
The reason for this is no combination of the other columns could be unique in all cases.
e.g. If a history record is created twice in one day for a user from the same url.
Performance:
Depends on how the history table is used - If you only ever SELECT data from it by means of a query on userId, or date, or Url, then the ID column would serve no purpose.
However, if you ever perform any Update/Delete operations on the table then, the Id would be useful.
Regardless of current requirements, it costs you almost nothing to include the extra column now, and it's something I would always recommend.
如果您看不到明显的主键，那么只需添加一个自动递增的整数/身份列作为您的主键 - 这几乎适用于任何表。
在某些情况下可能有更好的选择，但大多数情况下，自动递增整数字段无论如何都是主键的不错选择。
事实上，我可能会建议使用自动递增整数/标识列作为用户表的主键 - 在某些情况下（例如已删除的用户），您可能希望有两行具有相同的用户名。
If you can't see an obvious primay key then just add an auto-incremending integer /identity column as your primary key - this goes for almost any table.
There may be a couple of situations where there is a better choice, but most of the time an auto-increment integer field is a good choice of primary key regardless.
In fact I would probably recommend an auto-incrementing integer / identity column as the primary key for your users table too - there are a couple of situations (e.g. deleted users) where you might want to have two rows with the same username.
你可以检查 这个。尽管这个问题比您的问题更通用，但它应该让您对什么应该是主键有更广泛的了解。
you could check this one. Though this question is more generic than yours, it should give you a wider view on what should be primary keys.
我的猜测是历史表的可能键是（用户名、日期、网址）。然而，这只是基于属性名称的猜测。您应该根据业务分析和您的需求来确定密钥。
My guess is the likely key of the history table would be (username, date, url). This is only a guess based on the names of the attributes however. You should determine the keys based on business analysis and your requirements.