主键和代理键有什么区别? [英] What is the difference between a primary key and a surrogate key?

问题描述

我用谷歌搜索了很多，但我没有找到一个例子的确切直接答案.

I googled a lot, but I did not find the exact straight forward answer with an example.

这方面的任何示例都会更有帮助.

Any example for this would be more helpful.

推荐答案

主键是您选择的表中的唯一键，可以最好地唯一标识表中的记录.所有表都应该有一个主键，因为如果您需要更新或删除记录，您需要知道如何唯一标识它.

The primary key is a unique key in your table that you choose that best uniquely identifies a record in the table. All tables should have a primary key, because if you ever need to update or delete a record you need to know how to uniquely identify it.

代理键是人工生成的键.当您的记录基本上没有自然键时(例如 Person 表，因为同一天出生的两个人可能具有相同的名字，或者日志中的记录，它们很有用有可能发生两个事件，因此它们带有相同的时间戳).大多数情况下，您会看到这些实现为自动递增字段中的整数，或为每个记录自动生成的 GUID.ID 号几乎总是代理键.

A surrogate key is an artificially generated key. They're useful when your records essentially have no natural key (such as a Person table, since it's possible for two people born on the same date to have the same name, or records in a log, since it's possible for two events to happen such they they carry the same timestamp). Most often you'll see these implemented as integers in an automatically incrementing field, or as GUIDs that are generated automatically for each record. ID numbers are almost always surrogate keys.

与主键不同，并非所有表都需要代理键.如果您有一张列出美国各州的表格，则您实际上并不需要它们的 ID 号.您可以使用州缩写作为主键代码.

Unlike primary keys, not all tables need surrogate keys, however. If you have a table that lists the states in America, you don't really need an ID number for them. You could use the state abbreviation as a primary key code.

代理键的主要优点是它们很容易保证是唯一的.主要的缺点是它们没有任何意义.例如，28"没有意思是威斯康星州，但是当您在地址表的州列中看到WI"时，您就知道您在谈论哪个州，而无需查找您所在州的哪个州表.

The main advantage of the surrogate key is that they're easy to guarantee as unique. The main disadvantage is that they don't have any meaning. There's no meaning that "28" is Wisconsin, for example, but when you see 'WI' in the State column of your Address table, you know what state you're talking about without needing to look up which state is which in your State table.

这篇关于主键和代理键有什么区别?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！