Primary Key Best Practices

[Elegabalus]

I was always taught in my classes that the best way to create a primary key in a table was to use a unique integer (i.e., an identity). This way, the joins between tables were on an integer, and thus fairly fast.

Now, I'm working on a database that someone else designed, and they are using a concatenation of three char fields (2, 3, and 18 characters) as the primary key. This means, of course, that the foreign keys in the other dozens of tables have to be concatenations of the same fields.

I prefer to use identity fields as primary keys when I'm creating my own tables, but I'm wondering if there are any real downsides to a concatenated primary key.

Is there much of a difference, in terms of speed? Are indexes affected by this?

Any guidance is appreciated.

 

[SQLSister]

Speed of joins is poorer and of course the amount of space needed to store the information is greatly increased when you use concatenated PK fields. If you read about how indexes are stored, the narrower the index fields the better as it will use less space and cause fewer logical reads and all sorts of other good stuff. I personally would never consider using a concatenated primary key, especially if the tables involved will be large.

Also those nasty concatenated keys tend to be fields that might change over time like say Company name. THis will trigger lots of cascading changes that would never happen with an interger key.

Of course I would almost never use a Char (18) field either. I'll bet there are lots of entries in that field which don;t have 18 characters. VArchar is usually a better choice for longer fields containing string data.

The only upsaide I can see to using a concatenated key is to prevent having to use joins when querying the foreign key table. Of course you can do this by denormalizing as well and still have the performance benefits of the integer key field when you need to query multiple tables.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[EdwinGene]

Redundant Data.

If you change one of the elements of the key you have to change it in every record of every table where it appears. And with Foreign Keys, the order in which you change the keys is important and a cascading change can get ugly real quick if the cascade goes down a few levels.

 

[Elegabalus]

Ok, good...

I thought concatenating three char fields was a bit of a stupid way to set a primary key.

I can understand the idea behind denormalizing the data, but it's really not significant info...just a way to uniquely identify the field, so there's not even that excuse for the person that designed this db.

Thanks for the info.