>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.
>
>(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?

Create a Stored Procedure on the back-end system that returns the next available key stored in a Primary Key table for the specifed table. The SP could initialize the appropriate transaction locking on the Key table and not touch the data table.

Depending on how you locked the data table, simple read-only queries could not be performed while someone was trying to insert a record.

HTH.