GridView edit problem If primary key is editable (design problem)

Question

I would like to ask about the design of table based on it's editability in a Grid View. Let me explain. For example, I have a table named ProductCustomerRel.

Method 1

CustomerCode varchar  PK
ProductCode varchar  PK
StoreCode varchar PK
Quantity int
Note text

So the combination of the CustomerCode, StoreCode and ProductCode must be unique. The record is displayed on a gridview. The requirement is that you can edit the customer, product and storecode but when the data is saved, the PK constraint must still persist.

The problem here is it would be natural for a grid to be able to edit the 3 primary key, you can only achieve the update operation of the grid view by first deleting the row and then inserting the row with the updated data.

An alternative to this is to just update the table and add a SeqNo, and just enforce the unique constraint of the 3 columns when inserting and updating in the grid view.

Method 2

SeqNo int PK
CustomerCode varchar
ProductCode varchar
StoreCode varchar
Quantity int
Note text

My question is which of the two method is better? or is there another way to do this?

Answer 1

I would always encourage you to use Method 2. It makes life less complicated. The term for that kind of primary key is a Surrogate Key, as opposed to a natural key which is based on other fields which have another purpose other than being a primary key. You can still enforce a unique constraint on the 3 foreign key fields at the database level without having to use them in the primary key.

Have a read of this article http://www.wingenious.com/article1.html and then if you are interested in this, here is a series of articles on the same subject: http://www.wingenious.com/articles.html