I have recently been looking at a database and wondered if anyone can tell me what the advantages are supporting a unique collumn, which can essentially be seen as the primary key, with an identity seed integer primary key.
For example:
id [unique integer auto incremented primary key - not null],
ClientCode [unique index varchar - not null],
name [varchar null],
surname [varchar null]
isn't it just better to use ClientCode as the primary key straight of because when one references the above table, it can be done easier with the ClientCode since you dont have to do a lookup on the ClientCode everytime.
Regards
Mike
Why does everyone confuse a "primary key" and a "unique constraint"? They are totally different things. A PK is UNIQUE and ORDER of a table, which is also a "unique constraint". The PK should be the field (NOT fields) you use to LINK the table to other tables.
I have been doing databases for a very long time and I have several rules, which I have developed over the years on creating tables:
EVERY table has an integer id field, even if it is not needed right now, it will be later -- integer searching is 10,000 times faster than character searching and the indexes are much smaller.
NEVER key tables on data the user can change -- If you use ClientCode in your example above, and you change ClientCode, you would need to change EVERY SINGLE table which links the table using ClientCode.
|||
First of all Tom I have to disagree with the "ORDER of a table" statement. The order of a table is based on the clustered index butI do agree in most cases the primary key is the clustered key but this is not mandatory.
Most of the times when people tend to use non integer fields as a primary key it is because there is a suitable natural key for a table. I prefer natural keys if they fulfill the requirements for a primary key and that is unique, narrow and static (one might say ever increasing but then we would be back at the point of the clustered index story). Your example clearly shows the static requirement.
WesleyB
Visit my SQL Server weblog @. http://dis4ea.blogspot.com
|||That is true. A PK is usually a clustered index, although it is not required to be, in SQL Server. Other database engines may require it to be clustered. Generally, the PK is a clustered index, because it is field most searched, it improves performance by having the table ordered by the PK.In the case where "ClientCode" can NEVER EVER change, other than speed, there is nothing wrong with using it. However, I personally would not use it.
|||Hi,
Thanks for your replies, I appreciate it. I know about the performance bonus that one gets by using a numeric primary key vs a varchar but I was curious to know the possible advantages of why one would sometimes not use it especially since the 'ClientCode' is not controlled within my database and is actually a primary key from a table in another database that I am pulling data from.
Personally I have always been using an integer autoincremented primary key and placed an unique index on the 'ClientCode' but I became curious to know why people don't always do it after seeing some other databases where they use the 'ClientCode' as a primary key.
Regards
Mike
|||
The are several different arguments around natural keys vs artificial or surrogate keys. The one Wesley mentioned where you don't have a column that's practical to use as a key. And the issues you are aware of in terms of performance. And another argument is that what is a natural key for a table may change over time - business needs and rules change, external sources may change, etc. If you use an artificial key (generated number in your case), that won't change in terms of being the unique identifier. You can do a search on the two terms and find many articles that debate this. It's one of those ongoing, long standing debates in the relational database world. Some people just prefer one over the other depending on which side of the "debate" they believe in.
-Sue
|||In your case where ClientCode is not controlled inside you database, that qualifies as "user data" and I would never use that field to link to other tables in my database. I would create an internal key to use as the FK.What happens to your data if ClientCode is a varchar(10) now and the vendor decides to change it to a long? Or worse, allowes duplicates at some point.
|||Hi there,
Thank you all for your valuable input. I can understand why this might have been an ongoing debate since some people prefer simplicity over performance etc and I, for one, tend to be more of an 'performance' person so I will keep on using an seeded primary key.
It was good to hear the opinion of everyone here.
Thanks a lot
Mike
