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.)