Hi andy,

>>I am not very clear about:

>The purpose of a primary key is to uniquely identify a record in a table. You can either use a surrogate key (in which case the actual value does not have any meaning - like the simple integer you refer to) or a business key where some unique identifier is used (Acount number, Order Number and so on).

>There are two problems with using business keys:

>First is that they are usually governed by rules that require checking and validation. Whereas, the only rule that is really required for a PK is that it is unique in the current table. Applying additional rules to PKJs usually means additional (usually convoluted) code!

In general, you won't be solving any problems with surrogate keys here. If you use a surrogate key in a item table, the Itemno with be a CANDIDATE KEY. here you still have to do checkings for uniqueness in the very same way you would to for intelligent keys.

>Second is that, at some point, someone will want to change the key - either because a 'wrong' value was assigned, or because the Company is changing the format of its Account Numbers, Order Numbers etc! This immediately gives you a major RI issue because you now have to cascade changes through all related tables.

Yeah, so ? Therefore we have a mechansim that is called cascading updates. In an event of updates on PKs they can be cascaded troughout the system without much problems. You don't have to code anything, you must define it in the database RI builder.

Second question: how different is a cascading update from a cascading delete ? Surrogate eliminate the need of cascading updates, but they still need cascading deletes.

>>1. Since, filtered index is not optimize, how am I going to re-use the deleted candidate key but not effect the speed?
>
>Why would you re-use a candidate key any more than a primary key?

If you've previously deleted an item, but want to re-input it at a later stage.

>If I understand you, you are talking about things like Customer Numbers, Order Numbers and so on. If you are thinking about having to issue numbers for transactions that get cancelled, this is merely another reason for using surrogate keys. By using a surrogate key, you can still generate all the related records WITHOUT having to issue the customer number, order number or whatever, until the user actually commits the transaction. That is the whole point of the surrogate key.

err. could you explain this a bit further ? I don't seem to get your point here.

>>2. If I have a auto-generated field like Invoice No., do you think it is good to be primary key or candicate key??

>But you have answered the question yourself, no, it isn't. Presumably invoice numbers MUST be sequential, with no gaps (at least, as I understand it that is the normal practice). That 'rule' does not apply to a Primary Key whose only function is to identify a record, so an invoice number (irrespective of how it is generated) is NOT a good choice for a PK.

I don't get this. There are no unique invoiceno's so how would this disqualify for a PK ?


Also, you are forgetting the downsides of PKs:

1. They ussually require more joins in queries, limiting performance.
2. Its harder to debug and interpretate the content of your table on the fly.
3. Some rules like "You cannot rename an item unless it has not been sold already" you can implement with RI when using intelligent keys, you must find alternatives for surrogate cases.

see message Thread #601821 Message #603370 for additional info.

Walter,