I want to update the value of a primary key column in a database row. I tried setting the column's property in the entity instance to the new value. I got this error message:

The field 'field name' is part of a Primary Key and can't be updated after it is created in the persistent storage

I know I could get round this by deleting the row and then reinserting it. But is there a way of doing it with an actual update and avoid having to do the delete/insert?

SimonORorke wrote:

I want to update the value of a primary key column in a database row. I tried setting the column's property in the entity instance to the new value. I got this error message:

The field 'field name' is part of a Primary Key and can't be updated after it is created in the persistent storage

I know I could get round this by deleting the row and then reinserting it. But is there a way of doing it with an actual update and avoid having to do the delete/insert?

Updating a PK of an existing entity is not possible, and this is by design.

Otis wrote:

Updating a PK of an existing entity is not possible, and this is by design.

Is it going to stay that way?

Would it be possible to have maybe a pkIsReadOnly type property? So we can at least override it if need be, or is likely to break something else?

netclectic wrote:

Otis wrote:

Updating a PK of an existing entity is not possible, and this is by design.

Is it going to stay that way?

Would it be possible to have maybe a pkIsReadOnly type property? So we can at least override it if need be, or is likely to break something else?

Changing a PK is semantically wrong. The problem is that it can orphan foreign keys which ends up in a lot of misery and that it can make it impossible to save an entity. A PK is identifying an entity, however how are you going to identify the real entity in the database once you've changed the pk value? Say, you are able to modify CustomerID in Customer.

myCustomer.CustomerID = newValue; myCustomer.Save();

However, this will not work, because which row should be updated, the PK value has changed. You can then argue "keep the old values", but this is a hack, as the entity data semantically has received a new PK value, and thus a new identifying value.

The only situation where you'll run into this is when a user has to correct a typo in a PK value. If that's possible, or a necessity, it's then perhaps wise to switch to artificial keys and to place unique constraints on the column.

Otis wrote:

Changing a PK is semantically wrong.

Granted, but occassionaly it's something that you have to be able to do.

I think the ability to do it should be there, but it should not be allowed by default. As long as we know that doing it could cause problems... i know that in my case, the scenarios where i might need to change the pk value will not cause problems with related data because there are no relations defined on these tables.

I haven't tried it, but at first glance, it should work:

• create a predicate which filters on the PK of the object you want to change its PK value of.
• create a new entity, set the PK value. (either by the property or by ForcedCurrentValueWrite() method of the field, if you're dealing with identity columns)
• call UpdateEntitiesDirectly() on adapter or UpdateEntities() on a collection in selfservicing. This will update the PK field of the entity matching the filter.

The update query creation routine doesn't check on PK values or readonlyness, as the entities do that themselves.

Otis wrote:

The problem is that it can orphan foreign keys

Yes. But in more recent versions of SQL Server, it has become possible to define a foreign key with ON UPDATE CASCADE. In this case, when the primary key of the referenced row changes, the foreign key on all the referencing rows change automatically. If any foreign key that references the primary key has not been defined with ON UPDATE CASCADE, an attempt to update the primary key would fail with a meaningful error message. In the old days, before ON UPDATE CASCADE was available, a three step procedure was necessary:
* create a row with the new primary key on the referenced table * update the foreign keys on all referencing rows * delete the row with the old primary key on the referenced tableAs the database now supports cascading updates, it would be nice if this could be implemented with LLBLGen Pro.

Otis wrote:

which row should be updated, the PK value has changed

The row with the primary key value as at the time the row was fetched, e.g. with FetchUsingPK.

Otis wrote:

The only situation where you'll run into this is when a user has to correct a typo in a PK value. If that's possible, or a necessity, it's then perhaps wise to switch to artificial keys and to place unique constraints on the column.

In that situation, I agree, artificial keys are the way to go. Even on a database where cascading updates are possible, it is more efficient to avoid them.

In my application, though, I'm changing the primary key of a history table which I do not expect ever to be referenced by other tables. It has a compound primary key including the date from which the attribute values in the row apply. The date value is what I want to be able to change: in the application, I have to allow for retrospective adjustments to the date from which a set of attributes applied. Maybe I should nonetheless have used an arbitrary primary key. I did not think it would be an issue.

Otis wrote:

I haven't tried it, but at first glance, it should work: - create a predicate which filters on the PK of the object you want to change its PK value of. - create a new entity, set the PK value. (either by the property or by ForcedCurrentValueWrite() method of the field, if you're dealing with identity columns) - call UpdateEntitiesDirectly() on adapter or UpdateEntities() on a collection in selfservicing. This will update the PK field of the entity matching the filter.

I am using SelfServicing. I cannot find UpdateEntities. I think you mean UpdateMulti. I tried the following code, in which ParameterName is the single primary key field of the Parameter table.

ParameterEntity newValues = new ParameterEntity();
newValues.ParameterName = "PK2";
//newValues.ParameterValue = "PK2";
FieldCompareValuePredicate filter = 
  PredicateFactory.CompareValue(
    ParameterFieldIndex.ParameterName, 
    ComparisonOperator.Equal, 
    "PK1");
ParameterCollection updater = new ParameterCollection();
int rowsUpdated = updater.UpdateMulti(newValues, filter);
MessageBox.Show("Rows updated: " + rowsUpdated);

The result was interesting. The number of rows updated, according to the return value of UpdateMulti, was 1. But when I checked the contents of the database, I found that nothing had changed. (The code works fine if I instead change the value of a field that is not in the primary key by commenting out line 2 and uncommenting line 3.)

Yeah I meant UpdateMulti() . Sorry for that.

Indeed interesting results, as the routine returns the number received from the ExecuteNonQuery() method of the ADO.NET command object.

I'll check it out.

I have the same behaviour indeed. I try to change a CustomerID in Northwind, it returns 1, which means 1 row affected, but no row is changed. Very weird. Will look into it.

Ok, found it.

When initially looking at the create update query routine I overlooked the routine which constructs the set of fields to update, (I took a glance at it, not a deep investigation, the whole code is somewhat complex). In that routine there is this check:

if((fields[i].IsChanged)&&(!fields[i].IsPrimaryKey)&&(!fieldsPersistenceInfo[i].IsIdentity))

so it will never update PK fields.

As the resultset is empty, the complete query is empty and the execution engine sees that and simply returns 1 to prevent a stall in a potential recursive save.

I'll try to add some kind of behaviour so you can (with a few hoops in place) change PK values, however it will be in the next runtime revision late may.

Thanks for you help and your positive response to our suggestions.

Otis wrote:

I'll try to add some kind of behaviour so you can (with a few hoops in place) change PK values, however it will be in the next runtime revision late may.

Has any functionality (with hoops) been added that allows the updating of primary keys? It appears we will be needing a method to do this; Go figure I did a search but did not see if anything was added to address this.

Yes. Check this out: (unittest which tests this behavior)

/// <summary>
/// Tests the change of a pk value of an entity that is already in the database
/// </summary>
[Test]
public void UpdatePkTest()
{
    ProductEntity newProduct = EntityCreator.CreateNewProduct(1);
    newProduct.TestRunId = _testRunID;
    Console.WriteLine("Current Product PK = {0}", newProduct.ProductId.ToString());
    bool result = newProduct.Save();
    Assert.IsTrue(result);

    // refetch from db.
    newProduct.Refetch();

    // alter PK
    newProduct.ProductId = Guid.NewGuid();
    Console.WriteLine("New Product PK = {0}", newProduct.ProductId.ToString());
    
    // resave the product.
    result = newProduct.Save();
    Assert.IsTrue(result);
}

so basicly just set the PK to a new value. This doesn't work with Identity fields however, as Identity fields are sequences and setting these to a value can cause problems.

I have just upgraded to LLBLGen Pro Version 1.0.2004.1 and tried updating a primary key. It is straight-forward and works fine. Thanks!

Thanks Otis! I will be checking this out today.

Is this supposed to work for adapter as well? I have had this problem for a long time in tables where I know there aren't any relations to the primary key. In my case its a compound primary key and I want to change one of the three field values. Here is the code that I'm using now which doesn't work. The value of FrontOrRear is what I'm attempting to change here.

public void gridEx_RecordUpdated(object sender, RowActionEventArgs e)
{
    GridEXRow row = e.Row;
    
    if ( e.Row.Table.Key == "PartsPool" )
    {
        int setId = (int)row.CompositeDataKeyValues[0];
        int productVersionId = (int)row.CompositeDataKeyValues[1];
        string frontOrRear = (string)row.CompositeDataKeyValues[2];
        SetPartEntity setPart = new SetPartEntity(setId, productVersionId, frontOrRear);
        setPart.IsNew = false;
        foreach(GridEXCell cell in row.Cells)
        {
            object cellValue;
            string fieldName;

            if ( cell.DataChanged )
            {
                fieldName = cell.Column.DataMember;
                cellValue = cell.Value;

                // Data field values don't allow null, convert to empty string.
                if ( cellValue == null )
                    cellValue = string.Empty;

                setPart.SetNewFieldValue(fieldName, cellValue);
            }
        }
        ServiceFactory.GetPersistanceManager().SaveEntity(setPart);
    }
}

There's something wrong with the query producer in this particular scenario. I get an empty query being generated which doesn't get executed. Looking into it. Oh my I make the same mistake as you made in my test .

PK updating only works when the entity is fetched first. This is caused by the fact that the PK filter creator has to have the original value of the PK field, which is stored in DbValue. If DbValue is set, it's used for the filter, otherwise the CurrentValue, if the PK field is changed.

To avoid a fetch, use: setPart.Fields["fieldName"].ForcedCurrentValueWrite(newCurrentValue, dbValue);

you then have to set IsChanged to true, if you want the field to be changed.