Naming convention for unique constraint
Solution 1
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 2
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 3
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 =)
Kirk Broadhurst
I'm a data engineer who is passionate about learning new ways of doing things. I enjoy SQL and Python, and I particularly enjoy algorithms and solving problems. My spare time is spent with my kids. If there's anything left, it's split between motorsport, music, and learning/reading. I enjoy politics and have been known to play the odd video game.
Updated on January 05, 2022Comments
-
Kirk Broadhurst over 2 years
Naming conventions are important, and primary key and foreign key have commonly used and obvious conventions (
PK_Table
andFK_Table_ReferencedTable
, respectively). TheIX_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 recommendingAX_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 aUK
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 over 13 years@Mitch Any reason why? I do too but I always ask myself why noone just uses
U
. What's theQ
stand for? -
Chris J over 13 yearsYou could ask the same question about IX for IndeX ... why not just I?
-
Mark Cidade over 13 years"UQ" is just an abbreviation of "UNIQUE". The reason for two letters is basically it's a set precedent by "PK".
-
Matt almost 8 yearsPersonally 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.
-
CodingYoshi almost 7 years@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.
-
-
Hamish Grubijan about 11 yearsWould you strongly prefer "IXU" over "UIX"?
-
Alex KeySmith over 10 yearsAK: Alternate Key is what SQL Server Data tools 2012's design surface calls them too.
-
T.J. Crowder over 8 years@AlexKeySmith: Thank you for explaining why Microsoft uses AK for this!!
-
Alex KeySmith over 8 yearsNo problem @T.J.Crowder :-)
-
Nicholas Carey over 8 years@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.
-
T.J. Crowder over 8 years@NicholasCarey: No, I got it -- as soon as I saw Alex's comment, the light went on. :-)
-
Stefan Steiger over 7 years
-
The Impaler almost 6 yearsI'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.
-
Nicholas Carey almost 6 years@TheImpaler: In SQL Server (topic at hand), a unique constraint creates a unique index. That's how SQL Server enforces the constraint.
-
The Impaler almost 6 years@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.
-
Nicholas Carey almost 6 yearsYes, 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.)