单个自动编号而不是多个键 [英] Single Autonumber Instead of Multiple Key

问题描述

是否有理由为主键而不是实际表示唯一记录的多个字段使用单个递增字段？

我正在处理一个现有的php应用程序，表中似乎有一个id键，而不是使用实际上是唯一的2个或更多个字段记录（如用户，拍卖，出价）。

我不是数据库专家，但是对我来说似乎很懒惰（或没有经验）。有没有任何好处（表现或其他）？

已更新：我不是指psudo-unique数据（ssn，电子邮件地址等），您可能希望确保数据真正独一无二。我在谈论具有明显的外键引用的表，但是不是使用这些引用以及表中的唯一字段，每个表只有一个递增的ID。

不尝试开始一个主观的辩论，这对我没有意义。

解决方案

使用合成主键有几个优点：

• 您可以更改关键字段中的值，而无需使用索引更新命中


• 索引较小


• 它使外键关系更简单


• 由于您没有处理字符串，所以从来没有编码问题

数据库通常对构建索引的单调递增键具有特定的优化。

Is there a reason to use a single incrementing field for a primary key instead of multiple fields that actually represent the unique record?

I'm working on an existing php application, and the tables all seem to have a single 'id' key instead of using the 2 or more fields that are actually unique to the record (like user, auction, bid).

I'm not a database expert, but that just seems lazy (or inexperienced) to me. Is there any benefit (performance or otherwise)?

Updated: I'm not referring to psudo-unique data (ssn, e-mail address, etc), where you may want to ensure the data is really unique. I'm talking about tables with obvious foreign key references, but instead of using those references along with the unique field(s) in the table itself, every table just has an incrementing ID.

Not trying to start up a subjective debate, it just didn't make sense to me.

解决方案

Using synthetic primary keys has several advantages:

• You can change values in key fields without having to take an index update hit
• The indexes are smaller
• It makes foreign key relationships simpler
• Since you're not dealing with strings, there are never encoding issues

Databases often have specific optimizations around building indexes with monotonically incrementing keys.

That being said, there is nothing wrong with a little denormalization now and then. If the use-case is clear and the tables are relatively small, do what's convenient.

这篇关于单个自动编号而不是多个键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！