当一组将存储在表中的值具有在整个系统中应该唯一的名称或代码时,是否应使用ID auto auto increment(int)的主键创建?
以州缩写的情况为例 . 除了一致性之外,表上ID的目的是什么,除了州名或缩写之外的主键是什么?
例如,如果来自发货地址的外键引用了不可变的状态缩写,那么...是否有自动增加int ID的目的?
状态缩写是一个很好的非增量主键的罕见示例,原因如下:
它们很小(2个字符)
他们不会改变
这组值相对静态 - 不太可能有新记录
仅仅因为自然键是唯一的并不能使它成为主键的良好候选者 .
即使是真实世界的独特 Value (如SSN),如果它们被人类输入,也可能是很好的候选者 . 例如,假设有人为一个人输入一堆相关数据,然后得到一封SSN错误的信 - 现在你不能只更新主键 - 你还需要更新所有外键!
您突出显示了单独表格的一个积极方面:一致性 . 这样做要容易得多:
CREATE TABLE dbo.States ( StateID TINYINT PRIMARY KEY, Name VARCHAR(32), Abbreviation CHAR(2) ); CREATE TABLE dbo.CustomerAddresses ( AddressID INT PRIMARY KEY, ..., StateID TINYINT NOT NULL FOREIGN KEY REFERENCES dbo.States(StateID) );
比具有触发器或检查约束更像:
CHECK StateAbbreviation IN ('AL', 'AK', /* 50+ more states/territories... */)
现在,有了像2字符状态缩写的静态和小的东西,这个设计可能更有意义,消除了缩写和一些代理ID之间的一些不必要的映射:
CREATE TABLE dbo.States ( Abbreviation CHAR(2) PRIMARY KEY, Name VARCHAR(32) ); CREATE TABLE dbo.CustomerAddresses ( AddressID INT PRIMARY KEY, ..., StateAbbreviation CHAR(2) FOREIGN KEY REFERENCES dbo.States(Abbreviation) );
这会将数据约束到已知状态集,允许您将实际数据存储在表中(这可以消除查询中的大量连接),实际上可以节省一些空间,并避免任何混乱的硬编码检查约束(或使用UDF的约束,或验证数据的触发器) .
总而言之,没有满足所有设计的神奇毯子答案 . 随着字符串变大,使用整数而不是仅存储字符串会更有意义 . 一个反例可以存储你的网络日志中的所有用户代理字符串 - 将一次存储相同的字符串并为其分配一个整数是很有意义的,而不是一遍又一遍地存储相同的255个字符的字符串 . 再次 .
其他可能使这种设计麻烦的事情:
如果您以后扩展到美国以外该怎么办?
暂时忘掉状态缩写(非常静态);如果您的查找是经常更改的内容怎么办?
作为一般规则(可能不适用于每种情况),出于性能原因,最好将整数用作主键 . 因此,如果您的唯一键是字符串,请创建自动增量主键 .
而且,州不一定是唯一的 . 在一个国家/地区确实如此,但是当您查看世界上所有国家/地区时,可能会出现相同的缩写 .
EDIT
我找不到字符串与整数性能的非常好的证据,但请看一下,例如在这里:Strings as Primary Keys in SQL Database
话虽如此,从来没有很多州,所以在这种情况下,性能增益会很小 .
3 回答
状态缩写是一个很好的非增量主键的罕见示例,原因如下:
它们很小(2个字符)
他们不会改变
这组值相对静态 - 不太可能有新记录
仅仅因为自然键是唯一的并不能使它成为主键的良好候选者 .
即使是真实世界的独特 Value (如SSN),如果它们被人类输入,也可能是很好的候选者 . 例如,假设有人为一个人输入一堆相关数据,然后得到一封SSN错误的信 - 现在你不能只更新主键 - 你还需要更新所有外键!
您突出显示了单独表格的一个积极方面:一致性 . 这样做要容易得多:
比具有触发器或检查约束更像:
现在,有了像2字符状态缩写的静态和小的东西,这个设计可能更有意义,消除了缩写和一些代理ID之间的一些不必要的映射:
这会将数据约束到已知状态集,允许您将实际数据存储在表中(这可以消除查询中的大量连接),实际上可以节省一些空间,并避免任何混乱的硬编码检查约束(或使用UDF的约束,或验证数据的触发器) .
总而言之,没有满足所有设计的神奇毯子答案 . 随着字符串变大,使用整数而不是仅存储字符串会更有意义 . 一个反例可以存储你的网络日志中的所有用户代理字符串 - 将一次存储相同的字符串并为其分配一个整数是很有意义的,而不是一遍又一遍地存储相同的255个字符的字符串 . 再次 .
其他可能使这种设计麻烦的事情:
如果您以后扩展到美国以外该怎么办?
暂时忘掉状态缩写(非常静态);如果您的查找是经常更改的内容怎么办?
作为一般规则(可能不适用于每种情况),出于性能原因,最好将整数用作主键 . 因此,如果您的唯一键是字符串,请创建自动增量主键 .
而且,州不一定是唯一的 . 在一个国家/地区确实如此,但是当您查看世界上所有国家/地区时,可能会出现相同的缩写 .
EDIT
我找不到字符串与整数性能的非常好的证据,但请看一下,例如在这里:Strings as Primary Keys in SQL Database
话虽如此,从来没有很多州,所以在这种情况下,性能增益会很小 .