3

My application needs a generic lookup table Dictionary that should only be referenced by KEY {VARCHAR(N) UNIQUE}

Is there any reason I should not set KEY to be the primary key?

SilentGhost
  • 307,395
  • 66
  • 306
  • 293
Colton
  • 645
  • 4
  • 24
  • I guess obviously it's non standard to have a VARCHAR as the PK but I'm looking for more than it's simply nonstandard. – Colton Oct 31 '12 at 18:30
  • well if it gets really big and you have to join to it several times it will slow down your query speed – nathan hayfield Oct 31 '12 at 18:31
  • It's perfectly reasonable and perfectly standard. I'd say go for it. – Mike Christensen Oct 31 '12 at 18:32
  • How can it slow down queries Nathan. The point of a dictionary is that you are joining on a meaningful phrase and not a magic number. The queries would have to compare on string anyway. If I have an IDNENTITY the query would end up being O(n)+1 as it would have to lookup in the index then proceed to lookup in the table [AFAIK] as where having `KEY` as the PK would mean that the it would only lookup in the already sorted table. Am I correct? or does will it only keep an index of the VARCHAR and point it back to a row? – Colton Oct 31 '12 at 18:37
  • 2
    @Sparksis - Both indexes would use a B-TREE to find the key, however an integer key might be slightly faster because comparison operations between numbers are faster than between strings. However, I think you'd need an absolutely insane number of rows before you noticed *any* sort of difference. – Mike Christensen Oct 31 '12 at 18:43

1 Answers1

1

You can definitely use a VARCHAR as a primary key. But being a primary key it would be difficult to update in case of any chance. But as per your need you can definitely use VARCHAR as primary key

Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
  • What's wrong with updating a primary key? As long as it's not part of a sequence or anything, it should be no different than updating any other unique column. – Mike Christensen Oct 31 '12 at 18:37
  • @MikeChristensen:- That entirely depends on the data as I would consider primary key to be stable. If you are making changes to the primary key of the table then there are chances that the changes are becoming difficult in tables where it is used as a Foreign key. Correct me if I am wrong. – Rahul Tripathi Oct 31 '12 at 18:40
  • Your argument is perfectly valid, however it applies to referential integrity between *any* foreign keys. Sure, changes to data can potentially affect other tables, however this is the reason we have foreign key constraints in the first place. – Mike Christensen Oct 31 '12 at 18:49
  • @MikeChristensen, while in this case a varchar PK is nota bad choice, in general if you have FKs and tehy must be updated you go from updating one record to potentially millions of records, that is why people use surrogate keys. Yes you can set up on update cascade, but you stillhave to update all those recors. And if you don;t then you have no referntial integrity and your database is basically a very large paperweight. Also varchars are slower in joins than ints and so performance can be affected two ways. – HLGEM Oct 31 '12 at 18:58