2007-09-21

Why is a primary key an int?

We are used to having PKs in databases as integers.
The reason for this is that computers are good at numbers and an integer is a very versatile and fast number representation.

But there are drawbacks in the business layer.  A customerID can be compared to a orderID like so:

    if( customerID == orderID ){...

this is not a good thing.

CustomerIDs should only be comparable to other CustomerIDs.
They should also not be addable, subtractable, multiplyable and being greater than anything else.  A CustomerID is equal or not equal to another CustomerID.  All other comparisions are not of this world.

I have yet to figure out how to implement this.

3 comments:

Carl J said...

Why must a primary key be an int?

As far as I'm aware, any data type can be a primary key. Take for example, the aspnet_Users table (if you use the .Net membership provider), it's primary key (UserId) is a uniqueidentifier.

LosManos said...

Using a GUID as primary key might solve some problems.

One can still compare a GUID CustomerID to a GUID CustomerorderID; bad.
But one cannot retrieve a Customer through a CustomerorderID since no record will be found; good.

Anonymous said...

I don't agree.
Int IS faster, that's why primary keys should be int.
There will be a performance hit with GUIDs in a large database.
For no really smart reason, imho.
You can't always solve this kind of problem. What if the stupid junior programmer writes
if (sessionId == txtCustomerId.Text)
how would you avoid this kind of error in all situations, you don't! Sometime when you write code you just have to know what you are doing! :) /Ola.L.