主键过时了吗?

问题描述

主键提供哪些独特的功能?

虽然我用舌头牢牢地贴在脸颊上为问题命名，但我的问题很严重.在任何火焰开始之前，我并不是说构建一个没有约束或参照完整性的数据库.然而，据我所知，SQL Server 可以取消 primary key 关键字.

• 唯一索引涵盖了唯一性
• 基于列的非空性涵盖了 PK 的非空性要求
• PK 不必聚集在一起，所以不是这样
• 外键可以并且经常使用唯一索引而不是 PK 来实现
• 甚至 MSDN 都声明创建了一个唯一索引来强制执行 PK 的唯一性

我确实同意从逻辑上讲，主键表达了一些关于数据模型的意图，但就是这样吗?[讽刺]哦，我们确实在设计桌子时看到了 SSMS 显示的那个小钥匙图标！[/讽刺]

编辑

从评论来看，我似乎并没有像我想象的那样清楚地问这个问题.我同意主键从逻辑角度很重要.

我不问:

• 我应该为我的 PK 选择 int 还是 varchar
• PK 是否必须聚类，或者我如何确定应该聚类的内容
• 如何唯一标识行

我的目的是问PK 提供哪些功能不能合理使用其他功能实现?"我不是建议在这里发疯——比如使用触发器来强制唯一性而不是唯一约束/索引.合理是这里的一个关键词——使用唯一索引/约束似乎与定义 PK 非常相似.

一个完全不同的视角:

SQL 是一种由 ISO 标准定义的语言.该标准具有强制性"功能和可选一致性"功能.

如果您使用某种数据操作语言构建 DBMS，那么您只有在以下情况下才有权称您的语言为SQL":

(a) 您已经实现了标准规定的所有语法(强制"功能)，并且(b) 您已实现的所有语言功能(至少是所有强制性功能，还有您选择"的可选"功能)，准确地公开了标准中定义/描述的行为.

PRIMARY KEY"语法是一个非常古老的功能，而且它是那些强制性"的一个不太可能.从你的语言中删除这个词意味着你不能再合法地调用你的语言 SQL.大型商业供应商短期内不太可能采取这样的举措.

What unique functionality do Primary Keys provide?

While i titled the question with tongue firmly planted in cheek, my question is serious. Before any flames start, I'm not saying build a database without constraints or referential integrity. As far I can tell, however, SQL Server could do away with the primary key key word.

• Unique indexes cover, well, uniqueness
• Column based Non-nullability covers the non-nullability requirement for PKs
• PK's don't have to be clustered, so that's not it
• Foreign keys can, and often are, implemented with unique indexes, rather than PKs
• Even MSDN states that a unique index is created to enforce the PK's uniqueness

I do agree that logically a Primary Key coveys a bit of intention about a data model, but is that it? [sarcasm]Oh, and we do get that little Key icon SSMS shows when designing a table! [/sarcasm]

EDIT

From the comments, it seems clear I didn't ask this question as clearly as I thought. I agree that primary keys are important from a logical perspective.

I'm not asking:

• should i choose an int or a varchar for my PK
• do PK's have to be clustered, or how do i identify what should be clustered
• how do i uniquely identify rows

My intention was to ask "what features do PK's provide that cannot reasonably be implemented using other features?" I'm not suggesting going crazy here -- like using a trigger to enforce uniqueness instead of unique constraints/indexes. Reasonable is a key word here -- and using a unique index/constraint seems very similar to defining a PK.

A completely different perspective :

SQL is a language that is defined by an ISO standard. That standard has "mandatory" features and "optional conformance" features.

If you build a DBMS with some data manipulation language, then you are entitled to call your language "SQL" only if :

(a) you have implemented ALL of the syntax prescribed by the standard ("mandatory" features) , and (b) all of the language features that you have implemented (all the mandatory ones as a minimum, but also the "optional" ones you "opted in" for), expose exactly the behaviour as defined/Described in the standard.

The "PRIMARY KEY" syntax is a very old feature, and it's not unlikely that it is one of those "mandatory" ones. Ditching the word from your language means you can no longer legitimately call your language SQL. Big commercial vendors are not likely going to make such a move any time soon.

这篇关于主键过时了吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！