Edit Primary Key

Question

Is there a way of editing the primary key in MVC3 if the table only contains a primary key field. For example I have a console table and within it i have the console name as the Primary key and I want to be able to edit it and change it and save the edited value.

If there is any more info you require please let me know.

Answer 1

As a general rule, you should never edit primary keys. The primary key in SQL Server typically has a clustered unique index on it, so editing the primary key means you potentially have to rebuild your indexes (maybe not every time, but depending on the skew).

Instead I would create a fake primary key, such as an IDENTITY column in SQL Server, and put a UNIQUE constraint on the Name column. If your table grows large, retrieving items on an int column will also be faster than retrieving on a varchar() column.

Update: Since I was told I didn't answer the question (even though this is the accepted answer), it is possible to change the primary key values in SQL Server. But it is not technically an edit operation, since referential integrity may prevent a true edit (I haven't tried, so feel free to conduct your own experiment!)

The operation would go something like this:

1. Add a new row to the primary table, using the new PK value
2. Run an update operation to change all FK values to the new PK value
3. Delete the old PK row

I'd run all that in a transaction, too. But I will state again for the record, I do not recommend taking this approach.

Answer 2

As aKzenT pointed out, it is best to always use an Auto-Number/Identity or Sequence (Oracle) when defining primary keys. It is much more efficient for b-tree processors to find and join numeric keys, especially when textual ones are longer that a few bytes. Smaller keys also result in fewer b-tree pages that need to be searched.

Another important reason is that auto-generated keys cannot be modified. When using modifiable textual keys, foreign keys must employ CASCADE UPDATE which many (ex. Oracle, DB2) RDBMS do not support declaratively and must be defined using triggers, which is very complicated.

In your case, replacing the textual key with an auto-generated primary key will eliminate the problem.