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?

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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.