Primary key - Integer? or Varchar?

Question

Primary key - Integer? or Varchar?

sriniw8z

Old Hand

Points: 380
More actions
November 25, 2008 at 10:45 am

#215681

In sql server database design,I saw so many cases Primary key as Integer or AutoID, Is there any specific reson to have Integer (or Autogenerated ID) as primary key in database design, can we have in depth discussion to get clarity.

Viewing 15 posts - 1 through 15 (of 21 total)

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

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

This is a pretty big topic for a forum discussion. I would recommend you find a book on database design.

Integer / Identity columns are often used for primary keys in database tables for a number of reasons. Primary key columns must be unique, should not be updatable, and really should be meaningless. This makes an identity column a pretty good choice because the server will get the next value for you, they must be unique, and integers are relatively small and useable (compared to a GUID).

Some database architects will argue that other data types should be used for primary key values and the "meaningless" and "not updatable" criteria can be argued convincingly on both sides. Regardless, integer / identity fields are pretty convenient and many database designers find that they make suitable key values for referential integrity.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

And I would be careful when looking at natural key as a primary key. What might be unique and unchangeable today may not be so tomorrow as requirements change. If you do have a natural key as a primary key, I would add an integer (or decimal (n,0)) with an identity property to the record as well as an Alternate Key (unique). this way if the primary key has to change, you still have a unique way to identify each row that is independent of the data.

Also agree with getting some books and doing a lot of reading.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

Lynn Pettis (11/25/2008)
And I would be careful when looking at natural key as a primary key. What might be unique and unchangeable today may not be so tomorrow as requirements change. If you do have a natural key as a primary key, I would add an integer (or decimal (n,0)) with an identity property to the record as well as an Alternate Key (unique). this way if the primary key has to change, you still have a unique way to identify each row that is independent of the data.
Also agree with getting some books and doing a lot of reading.

Agreed with both previous posts. One additional piece which tends to play into SQL Server is that it will "double up" the Primary Key as the Clustered Index as well (unless the table already has one). How "big" the Primary key is then starts to play in at that point, and whether that might cause fragmentation, etc.... So - not directly germane to PK's, but is often a side-effect of a PK in SQL Server.

Oh - and before going much further - grab a bulletproof raincoat. This is one of those topics that have been known to enflame passions, so you may see bullets or mud start flying any time now....:)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Matt Miller (11/25/2008)
Lynn Pettis (11/25/2008)
And I would be careful when looking at natural key as a primary key. What might be unique and unchangeable today may not be so tomorrow as requirements change. If you do have a natural key as a primary key, I would add an integer (or decimal (n,0)) with an identity property to the record as well as an Alternate Key (unique). this way if the primary key has to change, you still have a unique way to identify each row that is independent of the data.
Also agree with getting some books and doing a lot of reading.
Agreed with both previous posts. One additional piece which tends to play into SQL Server is that it will "double up" the Primary Key as the Clustered Index as well (unless the table already has one). How "big" the Primary key is then starts to play in at that point, and whether that might cause fragmentation, etc.... So - not directly germane to PK's, but is often a side-effect of a PK in SQL Server.
Oh - and before going much further - grab a bulletproof raincoat. This is one of those topics that have been known to enflame passions, so you may see bullets or mud start flying any time now....:)

The other flying objects you might see are pork chops...

Back to Primary Keys. The PK may not be the best choice for the clustered index of a table (a table can only have one clustered index). You need to really look at the activity (selects/updates/inserts/deletes) that affect each table to really decide what makes a resonable clustered index. With that said, every table, from what I have learned, should have a clustered index as well as a primary key (index).

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 5

Great Lynn, take "should I use identity columns for a primary key" - a subject that will on it's own cause rage-full people to spew out angre messages and combine it with "what column(s) to use for a clustered index".

For anyone else that posts on this subject:

I couldn't fail to disagree with you less.

Johan Bijnens SSC Guru Points: 135265 More actions · Answer 6

- try to keep a primary key column meaningless, you'll not have to modify (datatype/length) it during the lifetime of the database. (var)Char always tend to get meaningfull after a while if you don't use a mystic algorithm to randomly chose values.

- Uniqueidentifiers have their own role and place in life if you want to spread data insertion all over your tables pages. You would have to leave enough free space in each page to avoid page splits. Sequential uniqueidentifiers are only sequential in the batch that is being executed.

- "independent" primary keys will get you single column foreign key references. Having your FK-columns indexed will speed up any parent key modification :crazy: or deletion check, as well as they will support joins !

Having single column joins is also a great advantage.

- and off course Rule number one for any (r)dbms: tell the system what you know. Correct datatyping will pay off in the long run. Clean data, proper indexing,....

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Michael Earl (11/25/2008)
Great Lynn, take "should I use identity columns for a primary key" - a subject that will on it's own cause rage-full people to spew out angre messages and combine it with "what column(s) to use for a clustered index".
For anyone else that posts on this subject:
I couldn't fail to disagree with you less.

Hey, don't blame me, Matt mentioned clustered indexes first while talking about Primary Keys. I just expanded upon the idea.

Besides, this discussion has remained relatively positive (so far).

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 8

Lynn Pettis (11/25/2008)
...Besides, this discussion has remained relatively positive (so far)...

In that case, let me jump in. 😀

There are many opinions of this subject, but I am firmly in the IDENTITY integer clustered primary key camp.

I have had many occasions to regret having natural keys as the primary key, but have yet to have reason to regret using IDENTITY integer for a PK. The best you can say about using natural keys as the PK is that sometimes they will not cause a problem.

So basically, anyone who advocates using natural keys is flat wrong. The gauntlet has been thrown; let the flames begin!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Michael Valentine Jones (11/25/2008)
Lynn Pettis (11/25/2008)
...Besides, this discussion has remained relatively positive (so far)...
In that case, let me jump in. 😀
There are many opinions of this subject, but I am firmly in the IDENTITY integer clustered primary key camp.
I have had many occasions to regret having natural keys as the primary key, but have yet to have reason to regret using IDENTITY integer for a PK. The best you can say about using natural keys as the PK is that sometimes they will not cause a problem.
So basically, anyone who advocates using natural keys is flat wrong. The gauntlet has been thrown; let the flames begin!

Actually, I agree with you, to a point. That point is that the primary key should be the clustered index. I firmly believe that "It Depends" when it comes to the clustered index.

Natural keys aren't necessarily the best choice for a primary key. What will never change, can.

sriniw8z Old Hand Points: 380 More actions · Answer 10

Dear Michael Earl, Thanks for your participation. what are the reasons you told to have Integer Or AutoGeneratedID as primary key are 100% true, but one problem is if we have Integer or autoID as primary key, there are some situations we need to create COMPOSITE PRIMARY KEY, by using integer or autoID we can avoid creation of COMPOSITE PRIMARY KEY, in this situation suppose we require to have 2 columns as unique, while updating it will update even if we use only one column in WHERE clause which leads to inconsistency. In this case explicitly we have to validate from front end.

Keep this problem in mind and if we create COMPOSITE PRIMARY KEY (with 2 or more columns as per requirements) by suppressing Integer or AutoID, in referencial integrity we have to create that many columns in FK table.

so do we need to take the decision based on situation and requirements or is there any way to avoid above kind of problems. Could you please.....

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 11

It may simple be a language issue with that last post, but I did not follow at all.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

The autoincrement column would be a primary key. This key would not be visible to the users, just to the database. It would be used to link related data together in the related tables (OrderHeader. OrderLine for example). Your composite key, which could be used as a primary key, would be an alternate key still uniquely identifying a row of data. The difference here is the alternate key could be visible to the users of the application. Also, if requirements change, and the key becomes non-unique, you don't totally break your system. It may be nothing more than dropping and recreating th alternate key making in non-unique.

Does that make sense?