主键和数据库规范化
可能的重复：
每个表都应该有一个主键吗？
我一直在从事一个关于数据库规范化的学校项目。 我需要帮助来标准化没有主键的表 我遇到困难的表是订阅表，其结构如下：
itemSubscribed emailAddress
-------------- ------------
1 [email protected]
1 [email protected]
1 [email protected]
2 [email protected]
2 [email protected]
3 [email protected]
请注意，itemSubscribed 和 emailAddress 值可能会重复，因此两者都不能成为主键。
此结构将与我的代码配合良好，因为当项目 X 中有更新时，我可以向所有项目 X 订阅者发送电子邮件，但我的老师需要标准化数据库，并且 1NF 必须有主键。
如果我为了拥有主键而创建了自动生成的主键，则无法继续使用 3NF，因为它要求所有列都依赖于主键，但情况并非如此。
我应该创建自动生成的主键吗？我是否遗漏了一些关于 3NF 的内容？
Possible Duplicate:
Should each and every table have a primary key?
I've been working on a school project about Database normalization.
I need help in normalizing a table that has no primary key
The table I'm having difficulty with is a table for subscriptions and it's structure is like this:
itemSubscribed emailAddress
-------------- ------------
1 [email protected]
1 [email protected]
1 [email protected]
2 [email protected]
2 [email protected]
3 [email protected]
Notice that itemSubscribed and emailAddress values may repeat, so neither can be a primary key.
This structure will work fine with my code for I can send an email to all item X subscribers when there's an update in item X but my teacher requires a normalized database and 1NF must have a primary key.
If I created an autogenerated primary key for the sake of having a primary key I can't proceed with 3NF for it requires that all columns are dependent upon the primary key, w/c is not the case.
Should I create a autogenerated primary key? Am I missing something in regards to 3NF?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
具有重复行的表并不代表关系。关系是一组元组。一个集合中的同一个元素永远不会出现多次。包就像一个集合，但可以有多个看起来相同的元素实例。
在您提供给我们的表中，我假设 itemSubscribed 是一个计数，并且 itemSubscribed 等于 1 且具有相同 emailAddress 的两行描述了不同的事件。
但这只是在你的脑海中，在数据中看不到。
这张桌子你会遇到麻烦的。特别是，无法区分错误的重复条目和两个看起来相似的有效条目。
A table with repeating rows does not represent a relation. A relation is a set of tuples. A set never has the same element in it more than once. A bag is like a set, but can have multiple instances of elements that look identical.
In the table you give us, I presume that itemSubscribed is a count, and the the two rows that have itemSubscribed equal to one with the same emailAddress describe different events.
But that is in your mind, and not visible in the data.
You are going to get into trouble with this table. In particular, there is no way to distinguish between an erroneous duplicate entry, and two valid entries that look alike.
您是否可以使用同一电子邮件地址多次订阅一件商品？如果不是，您的自然键是显而易见的：itemSubscribed 和 emailAddress。即使在这种情况下您选择使用人工主键，您也可能需要跨两列的唯一索引。
Are you allowed to have the same e-mail address subscribed to one item multiple times? If not your natural key is obvious: itemSubscribed and emailAddress. Even if you chose to have an artificial primary key in this case, you'd probably want a unique index across the two columns.
回答你的问题，是的，没有主键确实很糟糕。数据库必须有一种方法来识别特定记录。假设您想要更新下面以粗体显示的记录，而不是更新斜体记录。如果没有主键，您将如何做到这一点。
itemSubscribed emailAddress
1 [电子邮件受保护]
1 < a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="096b496a276a6664">[电子邮件受保护]
1 [email protected]
在数据库课程中，我会让你失败如果您有任何没有主键的表，那么它对于数据库设计至关重要。
现在我怀疑您实际上并不想拥有所示的数据，除非您有其他不同的列。为什么您真的想要两条订阅相同项目和相同电子邮件地址的记录？最好有一个 PK 或唯一索引来防止此类不良数据。我怀疑您确实拥有两个字段的自然键，但目前的数据不正确。
In answer to your question, yes it is really bad not to have a primary key. The database must have a way to identify a specific record. Suppose you wanted to update the record shown below in bold but not the one italics. How would you do that without a primary key.
itemSubscribed emailAddress
1 [email protected]
1 [email protected]
1 [email protected]
In a database class, I would fail you if you had any table without a primary key, it is that critical to database design.
Now I suspect that you would not want to actually have the data as shown unless you had other columns that were differnt. Why do you really want two records with the same items subscribed and the same email address? It is better to have a PK or unique index to prevent this sort of bad data. I suspect you really have a natural key of both fields and just currently have bad data.