When it comes down to primary keys, most of us that have been working with databases for a while have come across a few million different ones. Some work fairly well. Some just don’t work at all and should be banned from ever being used as a primary key again.
First, let’s delve into what is truly needed for a primary key. Let’s go back to the definition that I used in my previous post on ERDs. It is simply that “Primary Keys are the minimum amount of data that will make each row of a table unique.” From that very definition, we can deduce 2 things quickly.
- Primary Key fields have to have actual data
- The specific combination of all of the Primary Key fields has to be unique
The first point – “Primary Key fields have to have actual data” should be fairly self-explanatory. It simply means that there cannot be NULLs in any of the fields that make up the primary key. As we can see when we try to change a field in a SQL Server table to be a primary key and there are NULLs in it, SQL Server gives us an error message.
The second point – “The specific combination of all of the Primary Key fields has to be unique” – is always a fun one to deal with when you’re dealing with data, if your primary key isn’t set up correctly. For example, let’s look at the following:
As you notice, I tried to insert 2 rows with the same PersonID into the table. SQL Server says “Nope. Ain’t gonna happen” and tells me what I tried to insert multiple times. SQL Server sure is helpful, sometimes.
Now, to get back to the question that I asked in my last post – why isn’t the social security number the primary key?
There are several reasons.
- At many places of business, the social security number is not required – so you’d have to have a NULL or, even worse, a blank string – which fails the primary key definition.
- Social security numbers are not unique – at least not worldwide.
- Some people have more than one social security number. Rare, yes, but it does happen.
- If you go to another country, they’ll give you a temporary social security number – which could have been used by many other people already – again breaking the unique part of the definition.
- But most importantly, a person’s social security number is sensitive data, in this case, Personally Identifiable Information. Do not want to have to secure data that is also the primary key. That would be more than difficult.
So, next time, let’s go into another type of key that will help us out with ERDs. Until then…