Clever Geek Handbook

Change master key

Is there a way to edit the primary key in MVC3 if the table only contains the primary key field. For example, I have a console table, and inside 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 you need more information, please let me know.

+3


source to share


2 answers


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

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

Update: Since I was told that I did not answer the question (although this is the accepted answer), it is possible to change the primary key values ​​in SQL Server. But this is not technically an edit operation, as referential integrity can interfere with true editing (I haven't tried it, so feel free to do your own experiment!)



The operation will look something like this:

  • Add new row to main table using new PK value
  • Run an update operation to change all FK values ​​to the new PK value
  • Delete old PK line

I ran the whole thing in a transaction too. But I will reiterate on recording, I do not recommend using this approach.

+6


source


As aKzenT pointed out, it is best to use Auto-Number / Identity or Sequence (Oracle) when defining primary keys. It is much more efficient for b-tree processors to find and attach to numeric keys, especially when text keys are longer than a few bytes. Smaller keys also result in fewer b-tree pages to look for.

Another important reason is that the auto-generated keys cannot be changed. When using modifiable text keys, foreign keys must use CASCADE UPDATE, which many (eg Oracle, DB2) RDBMS do not declaratively support and must be defined using triggers, which is very difficult.



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

+1


source






More articles:


All Articles