LinqToSQL: Not possible to update PrimaryKey?

Question

I have a simple table (lets call it Table1) that has a NVARCHAR field as the PK. Table1 has no association with any other tables.

When I update Table1's PK column using LinqToSQL it fails. If I update other column it succeeds.

I could delete this row and insert new one in Table1, but I don't want to. There is a transaction table which has Table1's PK column as a column.

When the PK of Table1 is changed I want no effect in the transaction table. But when the row from Table1 is deleted, I want the transaction rows to be deleted. The cascading is done via Trigger.

As there is not association between these two tables, if I update the PK column of Table1 using normal SQL, it works and there is no effect on the transaction table as expected. When I delete the row the trigger deletes the rows from transaction table.

For this reason I can't delete and then add new row in Table1. So what can be done to successfully update the PrimaryKey of the Table1?

Answer 1

The Primary Key of a row is meant to uniquely identify a row (in the case of LINQ to SQL, an Object).

If you're modifying the Primary Key, in reality you're creating a new Unique Object with the same fields as another.

I always copy the original object with a new key, then delete the old object. It much more clearly states intent and prevents confusion.

Answer 2

As Otavio said, primary keys should not be updated. Create a column of type Int (or BigInt) and make it an auto numbering primary key. Your NVARCHAR field can simply have a unique constraint on it, because that is what you actually want, because the column is not used in relations with other tables.

Update:

Also note that when you use that column as a primary key and associate it with other tables, you cannot change it any more. SQL Server will throw an UPDATE statement conflicted with COLUMN REFERENCE constraint error in your face.

Next there are other issues with changing primary key, for instance: performance. Changing the primary key will lead to clustered index fragmentation.