数据库“密钥/ ID”设计思路，代理键，主键等 [英] Database "key/ID" design ideas, Surrogate Key, Primary Key, etc

问题描述

所以我最近看到几个提到替代密钥的提示，我不太确定它是什么，它与主键有什么不同。

我一直认为ID是我这个表格中的主键：

 用户
 ID，Guid 
 FirstName，Text 
姓氏，文本
 SSN，Int 
  

然而，维基百科将代理密钥定义为数据库中的替代密钥是建模世界中的实体或数据库中的对象的唯一标识符。代理密钥不是从应用程序数据导出的。 / p>

根据维基百科，ID是我的代理键，我的主键可能是SSN + ID？这是正确的吗？这是一个糟糕的表格设计吗？

假设表格设计是合理的，这样的事情会是坏的，对于一个数据没有什么特别的表格？

  LogEntry 
 ID，Guid 
 LogEntryID，Int [每次sql身份字段+1] 
 LogType，Int 
消息，文本
  

解决方案

p>不，您的ID可以是代理键（这只是意味着它不是从应用程序数据派生，例如人造键），和也应该是您的主键。 p>

主键用于唯一且安全地识别表中的任何行。它必须是稳定的，唯一的和NOT NULL - 人造ID通常具有这些属性。

我通常建议不要使用自然或实际数据主键 - 不是真的 150％确定永远不会改变？瑞士的SSN比例，例如每次女人结婚（或离婚）时都会改变 - 几乎不是理想的候选人。而且不能保证是独一无二的，或者......

为了摆脱所有的悲伤，只需使用系统定义的代理（人为）ID ，独一无二，永不改变，从来没有任何应用的意义（除了是你唯一的ID）。

Scott Ambler有一个很好的文章，里面有一个词汇表所有各种钥匙及其含义 - 您会发现自然，代理，主键等等。

So I've seen several mentions of a surrogate key lately, and I'm not really sure what it is and how it differs from a primary key.

I always assumed that ID was my primary key in a table like this:

Users
    ID, Guid
    FirstName, Text
    LastName, Text
    SSN, Int

however, wikipedia defines a surrogate key as "A surrogate key in a database is a unique identifier for either an entity in the modeled world or an object in the database. The surrogate key is not derived from application data."

According to Wikipedia, it looks like ID is my surrogate key, and my primary key might be SSN+ID? Is this right? Is that a bad table design?

Assuming that table design is sound, would something like this be bad, for a table where the data didn't have anything unique about it?

LogEntry
    ID, Guid
    LogEntryID, Int [sql identity field +1 every time]
    LogType, Int
    Message, Text

解决方案

No, your ID can be both a surrogate key (which just means it's not "derived from application data", e.g. an artificial key), and it should be your primary key, too.

The primary key is used to uniquely and safely identify any row in your table. It has to be stable, unique, and NOT NULL - an "artificial" ID usually has those properties.

I would normally recommend against using "natural" or real data for primary keys - are not REALLY 150% sure it's NEVER going to change?? The Swiss equivalent of the SSN for instance changes each time a woman marries (or gets divorced) - hardly an ideal candidate. And it's not guaranteed to be unique, either......

To spare yourself all that grief, just use a surrogate (artificial) ID that is system-defined, unique, and never changes and never has any application meaning (other than being your unique ID).

Scott Ambler has a pretty good article here which has a "glossary" of all the various keys and what they mean - you'll find natural, surrogate, primary key and a few more.

这篇关于数据库“密钥/ ID”设计思路，代理键，主键等的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！