160

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.

Kirk Broadhurst
  • 27,836
  • 16
  • 104
  • 169
  • 1
    @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 Jan 29 '11 at 10:56
  • 2
    You could ask the same question about IX for IndeX ... why not just I? – Chris J Jan 29 '11 at 12:19
  • 3
    "UQ" is just an abbreviation of "UNIQUE". The reason for two letters is basically it's a set precedent by "PK". – Mark Cidade Jan 29 '11 at 19:40
  • 2
    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 May 23 '16 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 Jul 17 '17 at 02:04

3 Answers3

186

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.

Nicholas Carey
  • 71,308
  • 16
  • 93
  • 135
  • 12
    AK: Alternate Key is what SQL Server Data tools 2012's design surface calls them too. – Alex KeySmith Aug 22 '13 at 12:24
  • 20
    @AlexKeySmith: **Thank you** for explaining why Microsoft uses AK for this!! – T.J. Crowder Aug 23 '15 at 13:31
  • No problem @T.J.Crowder :-) – Alex KeySmith Aug 24 '15 at 07:49
  • 1
    @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. – Nicholas Carey Aug 24 '15 at 16:59
  • @NicholasCarey: No, I got it -- as soon as I saw Alex's comment, the light went on. :-) – T.J. Crowder Aug 24 '15 at 18:04
  • I'm late to the party, but I always make a difference between "unique indexes" and unique constraints" those are two different things. They can show up simultaneously. – The Impaler Jun 27 '18 at 17:59
  • @TheImpaler: In SQL Server (topic at hand), a unique constraint creates a unique index. That's how SQL Server enforces the constraint. – Nicholas Carey Jun 28 '18 at 20:09
  • @NicholasCarey I guess that's pretty much everywhere. However, you can have different case where you create a unique index, without creating a unique constraint. – The Impaler Jun 28 '18 at 20:27
  • Yes, but the fact that it is unique makes it a *key*: a key being a set of attributes for the table such that the table is unique for that set of attributes: that is, no two rows in the table have the same values for that set of attributes. However, strictly speaking, and if you want to be pedantic, such a set of attributes is not a key if it itself contains a proper subset of attributes, that likewise defines uniqueness (e.g., the set of attributes that make up the key can't contain extraneous attributes that don't contribute to the uniqueness.) – Nicholas Carey Jul 02 '18 at 20:38
59

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...
gbn
  • 422,506
  • 82
  • 585
  • 676
10

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

bitxwise
  • 3,534
  • 2
  • 17
  • 22