Hi gar,


>1) The delete problem you mention should not cause a problem with meaningless pks generated either through incremtation or Guids. In neither case will new ids duplicate existng deleted records. Nor will you need to index the pk "FOR NOT DELETED".

You're shifting the problem to the entity that has to unique to (candidate key). You've either have to have a filtered candidate key (just like a filtered PK) or handle this in your business objects, which in fact not as simple as many wants us to believe (there are problems with checking if a value already exists, especially when using buffering and transactions, before the actual update or insert)

>2)You can have the problem you mentioned with candidate keys -- but this same problem will occur with meaningful pks -- in short not having the meaningless single field pk does eliminate this problem.

The check for uniqueness is done at savetime (because the database rejects the update because of a uniqueness violation). IMO this is the fastest, cleanest and most secure way to ensure a value (note: I'm talking about an intelligent value) is unique in a table. Any other method I consider as less reliable.

>3) In terms of changes -- yes you can use cascading changes and so forth to change meaningful keys. But god forbid a table is left out of your triggers. BTW, in terms of relations -- joins via single integer fields go one hell of a lot faster than joins via compound keys -- especially when you are joining different data types and have to do type conversions in the index expressions.

You're RI rules should be O.K. regardless if you use intelligent or surrogate keys. You seem to go by that the database itself is the key to a good application. If your database is not well designed, your application is not well designed.

>I first got the idea of using meaningless integer keys from an article by Date I read years ago in Dr. Dobbs. Over the years, this has proven a major league butt saver for me many many times...

Don't take me wrong, generated PKs have it's value, but it simply depends on the problem you're facing whether to use or not to use generated PKs. In the vast majority of cases generated PKs are very suitable and even reccomended (I won't denie this), but there are cases (as nadya points out: For lookuptables not functioning as a parent table adding an extra generated PK takes up space which can be very valuable. In order trying to avoid the 2GIG limit you might drop it.

Walter,