Adding an artificial primary key versus using a unique field

1
votes

Recently I Inherited a huge app from somebody who left the company.

This app used a SQL server DB .

Now the developer always defines an int base primary key on tables. for example even if Users table has a unique UserName field , he always added an integer identity primary key.

This is done for every table no matter if other fields could be unique and define primary key.

Do you see any benefits whatsoever on this? using UserName as primary key vs adding UserID(identify column) and set that as primary key?

3
sqlsql-serverdatabase
I personally do not see any benefits. Only thing I can think of is maybe search based on an int is faster? but im not an expert in Db optimization. Even if this is true unless table is gigantic different is negligible. - BTFman
There is an obvious performance benefit to having a key that fits inside a processor register, for both index maintenance and comparisons. That plus the ability to use an IDENTITY for such columns (which minimizes index fragmentation) means it's often worthwhile to do this, even if you're going to be adding UNIQUE constraints on the "real" keys anyway for consistency (since those separate indexes are easier to maintain than what is presumably the clustered index). There's also a big contingent of purists who think surrogate keys are the devil's toys, though. :P - Jeroen Mostert
@jeroen : The question is if this is needed for a small table or average size table. - BTFman
Sometimes there are domain specific reasons why a candidate key is not a good candidate (maybe people change user names so often that the required cascades start causing performance problems). But another reason to add an ever-increasing surrogate is to make it the clustered index. A static and ever-increasing clustered index alleviates a high-cost IO operation known as a page split. So even with a good natural candidate key, it can be useful to add a surrogate and cluster on that. See sqlskills.com/blogs/kimberly/… - allmhuran
(cont'd) but if you add such a surrogate, recognise that the surrogate is there for performance reasons only. It does not guarantee the integrity of your data. It has no meaning in the model, unless it becomes part of the model. For example, if you are generating invoice numbers as an identity column, and sending those out into the real world (on invoice documents/emails/etc), then it's not a surrogate, it's part of the model. If the identity is "purely internal", it's merely an optimization. - allmhuran

3 Answers

2
votes

You are talking about the difference between synthetic and natural keys.

In my [very] personal opinion, I would recommend to always use synthetic keys (and always call it id). The main problem is that natural keys are never unique; they are unique in theory, yes, but in the real world there are a myriad of unexpected and inexorable events that will make this false.

In database design:

  • Natural keys correspond to values present in the domain model. For example, UserName, SSN, VIN can be considered natural keys.

  • Synthetic keys are values not present in the domain model. They are just numeric/string/UUID values that have no relationship with the actual data. They only serve as a unique identifiers for the rows.

I would say, stick to synthetic keys and sleep well at night. You never know what the Marketing Department will come up on Monday, and suddenly "the username is not unique anymore".

1
votes

Yes having a dedicated int is a good thing for PK use.

you may have multiple alternate keys, that's ok too.

two great reasons for it:

  1. it is performant
  2. it protects against key mutation ( editing a name etc. )
1
votes

A username or any such unique field that holds meaningful data is subject to changes. A name may have been misspelled or you might want to edit a name to choose a better one, etc. etc.

Primary keys are used to identify records and, in conjunction with foreign keys, to connect records in different tables. They should never change. Therefore, it is better to use a meaningless int field as primary key.

By meaningless I mean that apart from being the primary key it has no meaning to the users.

An int identity column has other advantages over a text field as primary key.

  • It is generated by the database engine and is guaranteed to be unique in multi-user scenarios.
  • it is faster than a text column.
  • Text can have leading spaces, hidden characters and other oddities.
  • There are multiple kinds of text data types, multiple character sets and culture dependent behaviors resulting in text comparisons not always working as expected.
  • int primary keys generated in ascending order have a superior performance in conjunction with clustered primary keys (which is a SQL-Server specialty).