How to generate sql command to update primary key with other columns

Question

Postgres database contains product table with natural primary key:

create table product 
(
    productcode char(10) primary key,
    price numeric(12,2),
    ... a lot of other columns
);

and other similar tables with natural primary keys.

ASP.NET 5 MVC application is used to update it using EF Core with Npgsql data provider.

If product code is also changed, EF Core throws error

The property 'Product.Productcode' is part of a key and so cannot be modified or marked as modified. To change the principal of an existing entity with an identifying foreign key, first delete the dependent and invoke 'SaveChanges', and then associate the dependent with the new principal.

Product code is used as foreign key in other tables (with ON UPDATE CASCADE clause) so it cannot deleted.

Database structure change is not an option.

How to allow update of the primary key column also?

Some ideas:

1. Block this check in EF Core, e.q setting old value to same as new value forcibly before update.

2. Set primary key to some other value before saving changes.

3. Force EF Core to create update statement and execute it manually

4. Use some EF Core extension or other framework.

5. Change Npgsql EF core provider to allow this.

Which is best way to implement this without changing database structure?

Exception is thrown in line

https://github.com/dotnet/efcore/blob/f62cf1b1fa45d6026e8f98113d6d6712d81094c3/src/EFCore/ChangeTracking/Internal/ChangeDetector.cs#L107

   private static void ThrowIfKeyChanged(InternalEntityEntry entry, IProperty property)
    {
        if (property.IsKey()
            && property.GetAfterSaveBehavior() == PropertySaveBehavior.Throw)
        {
            throw new InvalidOperationException(CoreStrings.KeyReadOnly(property.Name, entry.EntityType.DisplayName()));
        }
    }

Is it OK to comment this out and compile new EF Core dll.

Answer 1

Since you are changing a primary key , as a matter of fact it is not updating , but adding a new product. So

1. Create a new product from existing one, that will have a new product code

2. Update ALL items from all tables that have the previous product code, replacing the previous foreign key, with the new one.

3. After this you can delete the previous product.

If you try to turn off validation and change the code, after this your db will be broken, and you will not be able to use it again, since you will constantly have the integration error.