何时不使用替代主键？

问题描述

我有几个数据库表只包含一个列和很少的行，通常只是在另一个系统中定义的某个ID。然后使用其他表中的外键引用这些表。例如，一个表格包含国家/地区代码（SE，DK，US等）。所有值始终是唯一的自然键，它们被用作其他（传统）系统中的主键。

似乎没有必要为这些表引入新的代理键，或者？

一般来说，代理键不应该使用的例外情况是什么？

解决方案

我会说以下条件必须满足：

• 你的自然密钥必须是绝对的，积极的，不允许的，唯一的（像名字，社会安全号码等通常似乎是独一无二的 - 但真的不是）




• 您的自然键应该与INT一样小，例如大小不超过4个字节（不要为您的PK使用VARCHAR（50），特别是不适用于SQL Server中的集群密钥！）

如果满足这些条件，您可以将自然密钥视为您的PK，但这应该是所有表中的2％异常 - 而不是规范。 / p>

Marc

I have several database tables that just contain a single column and very few rows, often just an ID of something defined in another system. These tables are then referenced with foreign keys from other tables. For example one table contains country codes (SE, DK, US etc). All values are always unique natural keys and they are used as primary keys in other (legacy) systems.

It seems really unnecessary to introduce a new surrogate key to these tables, or?

In general, what are the exceptional cases when surrogate keys shouldn't be used?

解决方案

I would say the following criteria must be met:

• your natural key must be absolutely, positively, no-exceptions-allowed, unique (things like names, social security numbers etc. usually seem to be unique - but really aren't)

• your natural key should be as small as an INT, e.g. not significantly more than 4 bytes in size (don't use a VARCHAR(50) for your PK, and especially not for your clustering key in SQL Server !)

• your natural key ought to be stable, e.g. never change (OK, with ISO country codes, this is almost a given - except when countries like Yugoslavia or the USSR collapse, or other like the two Germanies unite - but that's rare enough)

If those conditions are met, you can consider a natural key as your PK - but that should be the 2% exception in all your tables - not the norm.

Marc

这篇关于何时不使用替代主键？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！