dataset updates when primary key changes / concurrency issue

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Suppose for a moment I am using "natural" keys and there are the two tables
shown below.

Country (countryName)
LanguageSpokenInCountry (countryName, language)

Suppose I set up cascade updates and deletes between the two tables, in both
the dataset and database, so if a country is renamed, such as from "U.S.A."
to "United States", the corresponding rows in LanguagesSpokenInCountry are
updated.

The default Update commmand for the child table looks something like this:

UPDATE LanguageSpokenInCountry
SET countryName = @countryName, language = @language
WHERE ((countryName = @original_countryName) AND (language =
@original_language))

Now suppose someone modifies the dataset by changing the spelling of
"U.S.A." to "United States" in the parent table, and changes a mis-spelling
of "englsh" to "English" in the corresponding child table row. After the
parent row is updated in the database, the update on the child row fails and
generates a concurrency exception because by the time it gets executed there
is no row in the child table where countryName = the @original_countryName of
"U.S.A."

Now suppose I try to fix this by changing the default Update command to the
following command. Notice that now there WHERE clause looks for countries
matching the current dataset country.

UPDATE LanguageSpokenInCountry
SET countryName = @countryName, language = @language
WHERE ((countryName = @current_countryName) AND (language =
@original_language))

But this is only a partial fix. A problem remains in that whenever the
parent row in the dataset changes, a cascade update in the dataset causes the
child row to be marked as Modified, regardless of whether any other data in
the child row besides the countryName foreign key has changed. As a result,
the data adapter thinks it needs to run the Update command on the child row
when no changes may be necessary. I suppose this could be fixed by
pre-processing all "modified" child rows in the dataset before updating to
look to see if any data actually changed besides the foreign key, and if not,
simply accept the changes on the row. This seems like a hack to me.

What is the recommend way to approach this overall problem?
 
I don't think you should be allowing the primary key to change. If you want
to use a natural key, then you should not allow it to change once the row is
inserted. In theory you could then delete all the old rows and insert new
ones to mimic changing it. However, that is a lot of overhead.

Is there a reason you can't use a GUID or something like that as your
primary key?
 
I could use an identity field. I prefer having fewer columns if that will
work, identity columns add complexity on inserts since the new id must be
retrieved, and the textual name does uniquely identify the row. If someone
changes it they are likely keeping the identity constant but just changing
spelling.
 
Back
Top