Naming convention for unique constraint

Kirk Broadhurst picture Kirk Broadhurst · Jan 29, 2011 · Viewed 69.8k times · Source

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.

Answer

Nicholas Carey picture Nicholas Carey · Jan 29, 2011

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}

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:

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