我正在创建一个数据库表,我没有给它分配一个逻辑主键。每个表都应该有一个主键吗?


当前回答

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?

你说你没有自然的密钥,因此你也没有任何字段的组合来使其唯一,这使得人工密钥至关重要。

我发现,在没有天然密钥的情况下,人工密钥对于维护数据完整性是绝对必要的。如果您确实有一个自然键,您可以使用它作为键字段。但就我个人而言,除非自然键是一个字段,否则我仍然喜欢人工键和自然键上的唯一索引。如果你不放进去,你以后会后悔的。

其他回答

虽然来晚了,但我想补充一下我的观点:

每个表都应该有一个主键吗?

If you are talking about "Relational Albegra", the answer is Yes. Modelling data this way requires the entities and tables to have a primary key. The problem with relational algebra (apart from the fact there are like 20 different, mismatching flavors of it), is that it only exists on paper. You can't build real world applications using relational algebra. Now, if you are talking about databases from real world apps, they partially/mostly adhere to the relational algebra, by taking the best of it and by overlooking other parts of it. Also, database engines offer massive non-relational functionality nowadays (it's 2020 now). So in this case the answer is No. In any case, 99.9% of my real world tables have a primary key, but there are justifiable exceptions. Case in point: event/log tables (multiple indexes, but not a single key in sight).

总之,在遵循实体/关系模型的事务应用程序中,为几乎(如果不是)所有表设置主键非常有意义。如果您决定跳过表的主键,请确保您有一个很好的理由,并准备为您的决定辩护。

最好有一个主键。通过这种方式,它满足了第一种标准形式,并允许您继续沿着数据库规范化路径进行。

正如其他人所述,有一些理由不使用主键,但如果有主键,大多数情况下不会受到损害

几乎每次我创建一个没有主键的表时,我都认为我不需要主键,最终我会返回并添加一个主键。现在,我甚至用一个自动生成的标识字段作为主键来创建连接表。

我的职责是维护海外开发团队创建的应用程序。现在我在应用程序中有各种各样的问题,因为原始的数据库模式不包含一些表上的主键。所以请不要让别人因为你糟糕的设计而受苦。在表上设置主键总是一个好主意。

只要添加就可以了,以后你会后悔没有(选择,删除)。连接等)