>In an Client-Server application where the customer insists on a specific Primary key (say ID C(10)), I wish to discuss the pros and cons of the following methods of inserting a new record.
>
What do you mean by "the customer insists on"? Just that it be C(10)? or is there some other constraint to it - like a certain structure? I'm just asking because they may be asking for something that they really don't want...
>(a) Check if the new primary key (Say ID = "JFK"), exists in the Backend table using INDEXSEEK or SELECT-SQL, then lock the WHOLE table while the INSERT is being performed.
>(b) Try to insert the record without checking, trap and deal with any error returned ie "Duplicate Primary key"
>(c) Any other suggestions?
(c) Use a key generation table and always get the next available id from there using a stored procedure before inserting a new record. The stored procedure call can be stored in the field's default value, so if you need to add records directly to the table, you'll still get good PK values.
I believe this is the way the Tastrade sample app generates IDs...
Insanity: Doing the same thing over and over and expecting different results.