Naming convention for unique constraint

Question

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

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.

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

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.

@Mitch Any reason why? I do too but I always ask myself why noone just uses U. What's the Q stand for? — Kirk Broadhurst
– Kirk Broadhurst, Commented Jan 29, 2011 at 10:56
You could ask the same question about IX for IndeX ... why not just I? — Callie J
– Callie J, Commented Jan 29, 2011 at 12:19
"UQ" is just an abbreviation of "UNIQUE". The reason for two letters is basically it's a set precedent by "PK". — Mark Cidade
– Mark Cidade, Commented Jan 29, 2011 at 19:40
Personally I ended up using UX_* for "Unique indeX", in homage to the default of IX for "IndeX". I particularly dislike UK_ because I'm from the United Kingdom. I could be persuaded on AK if that's what the internet likes. — Tim Abell
– Tim Abell, Commented May 23, 2016 at 13:18
@KirkBroadhurst I have never seen such a convention but because of the upvotes it must be common and serve the purpose well. But having a foreign key named FK_03 is not very helpful, wouldn't it be better to name it FK_TargetTable_SourceTable? Can you please elaborate. — CodingYoshi
– CodingYoshi, Commented Jul 17, 2017 at 2:04

Nicholas Carey · Accepted Answer · 2022-01-04 22:50:19Z

My naming convention for indices and constraints:

Index/Constraint Type	Naming Convention
Primary key	<table-name>`_PK`
Unique index/constraint	<table-name>`_AK{xx}`
Non-Unique index	<table-name>`_IX{xx}`
Check constraint	<table-name>`_CK{xx}`
Default constraint	<table-name>`_DF{xx}`
Foreign key constraint	<table-name>`_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:

Suffix	Meaning
`PK`	Primary Key
`AK`	Alternate Key
`FK`	Foreign Key
`IX`	IndeX
`CK`	ChecK
`DF`	DeFault

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

AK: Alternate Key is what SQL Server Data tools 2012's design surface calls them too.
@AlexKeySmith: Thank you for explaining why Microsoft uses AK for this!!
@T.J.Crowder, in the entity-relationship/database modelling world, it is an alternate key, because, like the primary key, the column set comprising the alternate key must be unique, thus providing [alternate] identity for the row/tuple.
@EugenKonkov — No. A unique index is an alternate key. A set of columns that makes the table/entity unique is, by definition, either the primary key or an alternate key.

gbn · Accepted Answer · 2013-02-27 07:36:56Z

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

It could be used as a key of course, and uniquely identifies a row, but it isn't the key.

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

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

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_columnname for unique indexes and IX_columnname for non-unique indexes. Marvellous.

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

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

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...

bitxwise · Accepted Answer · 2011-01-29 10:56:21Z

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 =)

Collectives™ on Stack Overflow

Naming convention for unique constraint

3 Answers 3

10 Comments

2 Comments

Comments

Linked

Hot Network Questions