"JustinMagaram" <JustinMaga***@discussions.microsoft.com> wrote in message
news:EB83C600-67E0-45DB-B972-2E601D1D85CD@microsoft.com...
> 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?

"Marina" wrote:

> 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?
>
> "JustinMagaram" <JustinMaga***@discussions.microsoft.com> wrote in message
> news:EB83C600-67E0-45DB-B972-2E601D1D85CD@microsoft.com...
> > 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?
>
>
>

> 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?