Naming convention for unique constraint
SqlSql ServerNaming ConventionsUnique ConstraintSql Problem Overview
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.
Sql Solutions
Solution 1 - Sql
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.
Solution 2 - Sql
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...
Solution 3 - Sql
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 =)