SQL newbie, question about primary key.

Question

SQL newbie, question about primary key.

SerolfDivad

SSC Veteran

Points: 269
More actions
February 12, 2021 at 6:04 pm

#3844327
Hello, SQL newbie here. I've got a question about the proper use of primary keys, hoping someone can help out.
Let's say I establish an HR database and decide that, since Social Security numbers are unique and non-repeating, I'm gong to use SS numbers as my primary key. Something like this:
Name, Title, Social_Security_Number
Joe Smith, Accountant, 123-45-6789
Jim Jones, IT Staff, 321-54-9876
AmyWhite, Sales, 111-22-3333
Now, when an employee leaves the company I delete his entry in the DB. But let's say the employee returns a year later to a new position. I want to add him to the DB again. Is there a way to do that? Is there a way to force MS SQL to reuse a primary key? Or is this a good argument for having the Primary Key simply be a separate auto-incrementing column and having the SocialSecurity column simply be unique by constraint?
- This topic was modified 3 years, 2 months ago by SerolfDivad.
- This topic was modified 3 years, 2 months ago by SerolfDivad.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16011 More actions · Answer 1

Social Security numbers are considered PII and are a attribute of an individual/company and should be stored securely and are for this reason a bad choice for PK - other reasons exist but this alone should make you considering excluding it.

I'm sure others will jump in with other reasons NOT to use it

ratbak Ten Centuries Points: 1332 More actions · Answer 2

Such as they are not really unique: https://www.nbcnews.com/technolog/odds-someone-else-has-your-ssn-one-7-6C10406347

SerolfDivad SSC Veteran Points: 269 More actions · Answer 3

Thanks. The above DB is just a hypothetical. My real world scenario doesn't actually involve SS numbers, or people. It actually involves PKI certificates with RequestIDs. It's the request ID field that I'm considering making my Primary Key. It is a unique, non-repeating value and I'll never have to log two certificates with the same RequestID. I'm just worried that if I accidentally or mistakenly delete a row from the database (lets imagine I remove revoked certificates, and accidentally remove the wrong one) I won't be able to repopulate it with the correct certificate data since I won't be able to re-establish the correct RequestID field value.

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 4

Unless I am misunderstanding something here

ADD a record with a PKey value "X"

Delete that record with PKey "X"

Add a new record with PKey "X" is absolutely fine, as at that moment it does not exist.

Ed Wagner SSC Guru Points: 287024 More actions · Answer 5

Everyone's already addressed the SSN and I'm glad it was hypothetical. Kristin hit the nail on the head regarding the PK. The only thing I'd say to watch out for is having the clustering key column on something like name or SSN. Writing data to the somewhere in the leaf level other than the end can cause page splits unless you have sufficient free space available to take the new row.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 6

Ed Wagner wrote:

Everyone's already addressed the SSN and I'm glad it was hypothetical. Kristin hit the nail on the head regarding the PK. The only thing I'd say to watch out for is having the clustering key column on something like name or SSN. Writing data to the somewhere in the leaf level other than the end can cause page splits unless you have sufficient free space available to take the new row.

I know you know this, Ed, so I'm just saying it for the benefit of others...

It's not much of a problem if you have a decent Fill Factor, the newly inserted data is fairly random based on the clustering key, and you NEVER use REORGANIZE to maintain the index. REORGANIZE does NOT work the way people think it does. It's documented "correctly" for what it does but it uses a poor choice of words that causes people to believe something that it simply cannot do because it cannot create extra pages. That "something" is to bring pages back DOWN to the Fill Factor. It can only compress the index, which actually removes free space from the index when it is needed the most.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SerolfDivad SSC Veteran Points: 269 More actions · Answer 7

Thanks so much for you help, folks. In further testing I see you're absolutely right. I appear to have misdiagnosed an error that arose from an illegal character type being fed to my import script and not from the reuse of a deleted primary key.