SQL 唯一约束的命名约定

Naming convention for unique constraint

提问by Kirk Broadhurst

Naming conventions are important, and primary key and foreign key have commonly used and obvious conventions (PK_Tableand FK_Table_ReferencedTable, respectively). The IX_Table_Columnnaming for indexes is also fairly standard.

命名约定很重要，主键和外键有常用和明显的约定（分别为PK_Table和FK_Table_ReferencedTable）。IX_Table_Column索引的命名也是相当标准的。

What about the UNIQUE constraint? Is there a commonly accepted naming convention for this constraint? I've seen UK_TableName_Column, UQ_TableName_Column, and someone recommending AX_TableName_Column- I don't know where that comes from.

UNIQUE 约束呢？此约束是否有普遍接受的命名约定？我见过UK_TableName_Column, UQ_TableName_Column, 和有人推荐AX_TableName_Column- 我不知道那是从哪里来的。

I've typically used UQbut I don't particularly like it, and I do not enjoy having to defend my choice of using it against a UKadvocate.

我通常使用过UQ但我不是特别喜欢它，而且我不喜欢为我选择使用它而反对UK拥护者辩护。

I would simply like to see if there is a consensus on the most prevalent naming, or a good reasoning as to why one makes more sense than the others.

我只是想看看是否就最流行的命名达成了共识，或者是否有一个很好的理由来解释为什么一个比其他的更有意义。

采纳答案by gbn

My thinking is it isn't a key: it's a constraint.

我的想法是它不是一个关键：它是一个约束。

It couldbe used as a key of course, and uniquely identifies a row, but it isn'tthe key.

它可以被用来作为一个理所当然的键，唯一标识一行，但它并不是关键。

An example would be that the key is "ThingID", a surrogate keyused in place of ThingName the natural key. You still need to constrainThingName: it won't be used as a key though.

一个例子是，关键是“ThingID”，替代键到位ThingName自然键的使用。您仍然需要限制ThingName：尽管它不会用作键。

I'd also use UQ and UQC (if clustered).

我也会使用 UQ 和 UQC（如果集群）。

You could use a unique index instead and go for "IXU". By the logic employed, an index is also a key but only when unique. Otherwise it's an index. So then we'd start with IK_columnnamefor unique indexes and IX_columnnamefor non-unique indexes. Marvellous.

您可以改用唯一索引并选择“IXU”。根据所采用的逻辑，索引也是键，但只有在唯一时。否则它是一个索引。那么我们将从IK_columnname唯一索引和IX_columnname非唯一索引开始。奇妙。

And the only difference between a unique constraint and a unique index is INCLUDE columns.

唯一约束和唯一索引之间的唯一区别是 INCLUDE 列。

Edit: Feb 2013. Since SQL Server 2008, indexes can have filters too. Constraints can not

编辑：2013 年 2 月。自 SQL Server 2008 起，索引也可以有过滤器。约束不能

So, it comes down to one of

所以，它归结为其中之一

• stick with UQ as per the rest of the SQL-using planet
• use IK for unique indexes (IKC for clustered too) to be consistent...

• 按照 SQL 使用星球的其余部分，坚持使用 UQ
• 使用 IK 作为唯一索引（IKC 也用于集群）以保持一致...

回答by Nicholas Carey

My naming convention for indices and constraints:

我的索引和约束命名约定：

• Primary key. _PK
• Unique index/constraint. _AK{xx}
• Non-Unique index. _IX{xx}
• Check constraint. _CK{xx}
• Default constraint. _DF{xx}
• Foreign key constraint. _FK{xx}

• 首要的关键。_PK
• 唯一索引/约束。_AK{xx}
• 非唯一索引。_IX{xx}
• 检查约束。_CK{xx}
• 默认约束。_DF{xx}
• 外键约束。_FK{xx}

Where {xx} is a 2-digit sequence number, starting at 01 for each constraint type per table. Primary key doesn't get a sequence number since there can be only one. The 2-char alpha suffix meanings are:

其中 {xx} 是一个 2 位序列号，每个表的每个约束类型从 01 开始。主键没有序列号，因为只能有一个。2-char alpha 后缀的含义是：

• PK: Primary Key
• AK: Alternate Key
• FK: Foreign Key
• IX: IndeX
• CK: ChecK
• DF: DeFault

• PK：主键
• AK：备用钥匙
• FK：外键
• 九：指数
• CK：检查
• DF：默认

I generally want to group metadata/system catalog data by the controlling object rather than by object type.

我通常希望通过控制对象而不是对象类型对元数据/系统目录数据进行分组。

回答by bitxwise

I use UQ. The K in UK makes me think of K as it's used in PK and FK. Well, after I think of United Kingdom anyways; ironic that this should be a prefix for UNIQUE when UK brings up so many other associations =)

我用昆士兰。UK 中的 K 让我想到 K，因为它在 PK 和 FK 中使用。好吧，无论如何，在我想到英国之后；具有讽刺意味的是，当英国提出这么多其他协会时，这应该是 UNIQUE 的前缀 =)