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


当前回答

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

其他回答

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?

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

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

不同意建议的答案。简短的回答是:不。

主键的作用是唯一地标识表上的一行,以便与另一个表形成关系。传统上,用于此目的的是一个自动递增的整数值,但也有一些变化。

但在某些情况下,例如记录时间序列数据,根本不需要这样的键,只需要占用内存。使一行唯一很简单……不需要!

举个小例子: 表A: LogData

Columns:  DateAndTime, UserId, AttribA, AttribB, AttribC etc...

不需要主键。

表B:用户

Columns: Id, FirstName, LastName etc. 

用来作为LogData表的“外键”的主键(Id)。

我总是有一个主键,即使一开始我还没有想到它的目的。有几次,当我最终需要在一个没有PK的表中输入PK时,总是会遇到更多的麻烦。我认为总是有一个人会有更多的好处。

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

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

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).

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

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