Code Side Tower of Babel Update Problem.

I have some code that implements some business logic that sits on top of an
SQL Server Express 2005 DB.
The database is fine, but I have found a potential issue with the updates
that affects my code, and also the functionality of the .Net Dataset, that I
am looking for a way around.
My code is required to work with a large dataset of multiple related tables,
that are only committed on calling the Save() function. This functionality
works fine, but I have found an issue that is perfectly legal in the busines
s
logic, but will fail when attempting to store to the database due to unique
restrictions.
I have a table:
ID Name
1 AAA
2 BBB
The column Name is a unique column.
In the business logic, I can do the following:
* Rename Item[1] to "--"
* Rename Item[2] to "AAA"
* Rename Item[1] to "BBB"
* Call Save()
This is completely valid at the Dataset side, as no unique restriction is
breached. However, at this point, the program will begin iterating through
the updated value, and the following SQL is generated by the dataset:
UPDATE tbl SET Name = 'BBB' WHERE ID = 1
UPDATE tbl SET Name = 'AAA' WHERE ID = 2
The first item executed will be
UPDATE tbl SET Name = 'BBB' WHERE ID = 1
On Table
ID Name
1 AAA
2 BBB
The attempt to update the table to state
ID Name
1 BBB
2 BBB
Will fail, regardless of the fact that the next update will restore the
unique value status.
Is there any way to enable a before and after test on a total commit? Say by
using transactions?
This is a critical issue for my system.
Regards
Tris"Uri Dimant" wrote:

> Tris
> Do you have an unique constraint on (id,name) ?
Yes, in this instance the column Name is unique.
Although this is only an example, it could be applied to any unique set of
fields where this situation could arise.|||> This is completely valid at the Dataset side, as no unique restriction is
> breached.
That's because your Dataset constraints don't match the database. If you
had the same constraints on both the Dataset and database, you would get the
error when the change was made to the dataset rather than when data is later
saved to the database.

> Is there any way to enable a before and after test on a total commit? Say
> by
> using transactions?
I believe you want a 'deferred constraint checking' feature so that
constraint's aren't enforced until COMMIT. Unfortunately, this feature does
not yet exist in SQL Server. If this is important to you, consider
submitting this as a feature request via Connect:
https://connect.microsoft.com/SQLServer
One workaround is to remove the database unique constraint and implement the
unique restriction in your Save method by querying to ensure modified values
are unique before you commit. Another approach is to first delete all
modified rows and then insert with the new values. However, that can be
problematic if you also have foreign key constraints.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tris" <Tris@.discussions.microsoft.com> wrote in message
news:A5BD2ECD-557E-4EF1-A6E2-7208B03CD737@.microsoft.com...
>I have some code that implements some business logic that sits on top of an
> SQL Server Express 2005 DB.
> The database is fine, but I have found a potential issue with the updates
> that affects my code, and also the functionality of the .Net Dataset, that
> I
> am looking for a way around.
> My code is required to work with a large dataset of multiple related
> tables,
> that are only committed on calling the Save() function. This functionality
> works fine, but I have found an issue that is perfectly legal in the
> business
> logic, but will fail when attempting to store to the database due to
> unique
> restrictions.
> I have a table:
> ID Name
> 1 AAA
> 2 BBB
> The column Name is a unique column.
> In the business logic, I can do the following:
> * Rename Item[1] to "--"
> * Rename Item[2] to "AAA"
> * Rename Item[1] to "BBB"
> * Call Save()
> This is completely valid at the Dataset side, as no unique restriction is
> breached. However, at this point, the program will begin iterating through
> the updated value, and the following SQL is generated by the dataset:
> UPDATE tbl SET Name = 'BBB' WHERE ID = 1
> UPDATE tbl SET Name = 'AAA' WHERE ID = 2
> The first item executed will be
> UPDATE tbl SET Name = 'BBB' WHERE ID = 1
> On Table
> ID Name
> 1 AAA
> 2 BBB
> The attempt to update the table to state
> ID Name
> 1 BBB
> 2 BBB
> Will fail, regardless of the fact that the next update will restore the
> unique value status.
> Is there any way to enable a before and after test on a total commit? Say
> by
> using transactions?
> This is a critical issue for my system.
> Regards
> Tris
>|||Tris
>Yes, in this instance the column Name is unique.
Pehaps you need something like that
IF NOT EXISTS (SELECT * FROM Table WHERE Name =@.Name)
UPDATE Table SET name =@.name
"Tris" <Tris@.discussions.microsoft.com> wrote in message
news:0EF2AC17-1F9E-4F7B-86DB-3A4B6A809DC1@.microsoft.com...
>
> "Uri Dimant" wrote:
>
>
> Yes, in this instance the column Name is unique.
> Although this is only an example, it could be applied to any unique set of
> fields where this situation could arise.|||"Dan Guzman" wrote:

> That's because your Dataset constraints don't match the database. If you
> had the same constraints on both the Dataset and database, you would get t
he
> error when the change was made to the dataset rather than when data is lat
er
> saved to the database.
The unique restrictions are enforced in the dataset, but i use a tower of
babel switch,
X = 'X'
Y = 'Y'
Save
X = 'Q'
Y = 'X'
X = 'Y'
Save
The constraints on the dataset are never breached.
I can't delete the rows, it would cause far to many headaches. And
dissabling the restrictions would be a pain.
However, i suppose i could create a validate stored procedure...
What do you think of this:
SPValidateTable?()
#TempTable
(
VarChar Name,
Int Count
)
TempTable = SELECT DISTINCT Name, COUNT(Name) FROM Table
SELECT Count(Name) FROM #TempTable WHERE (Count > 1)
Return
If the result != 0
Throw exception.
It's a hack i know... but it could work.
How could this be implemented before / After commiting a transaction, so
that it could fail a transaction if the method failed? And how can it's scop
e
be handled in relation to the transaction?
Tris|||Tris wrote:
> I have some code that implements some business logic that sits on top of a
n
> SQL Server Express 2005 DB.
> The database is fine, but I have found a potential issue with the updates
> that affects my code, and also the functionality of the .Net Dataset, that
I
> am looking for a way around.
> My code is required to work with a large dataset of multiple related table
s,
> that are only committed on calling the Save() function. This functionality
> works fine, but I have found an issue that is perfectly legal in the busin
ess
> logic, but will fail when attempting to store to the database due to uniqu
e
> restrictions.
> I have a table:
> ID Name
> 1 AAA
> 2 BBB
> The column Name is a unique column.
> In the business logic, I can do the following:
> * Rename Item[1] to "--"
> * Rename Item[2] to "AAA"
> * Rename Item[1] to "BBB"
> * Call Save()
> This is completely valid at the Dataset side, as no unique restriction is
> breached. However, at this point, the program will begin iterating through
> the updated value, and the following SQL is generated by the dataset:
> UPDATE tbl SET Name = 'BBB' WHERE ID = 1
> UPDATE tbl SET Name = 'AAA' WHERE ID = 2
> The first item executed will be
> UPDATE tbl SET Name = 'BBB' WHERE ID = 1
> On Table
> ID Name
> 1 AAA
> 2 BBB
> The attempt to update the table to state
> ID Name
> 1 BBB
> 2 BBB
> Will fail, regardless of the fact that the next update will restore the
> unique value status.
> Is there any way to enable a before and after test on a total commit? Say
by
> using transactions?
> This is a critical issue for my system.
> Regards
> Tris
>
Assuming you know the two (or more) ID's that you want to swap:
UPDATE tbl
SET Name = CASE WHEN ID = 1 THEN 'BBB' WHEN ID = 2 THEN 'AAA' END
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||> What do you think of this:
> SPValidateTable?()
> #TempTable
> (
> VarChar Name,
> Int Count
> )
> TempTable = SELECT DISTINCT Name, COUNT(Name) FROM Table
> SELECT Count(Name) FROM #TempTable WHERE (Count > 1)
> Return
If your table is small enough that you don't mind checking the entire table,
I would suggest that you ditch the temp table:
CREATE PROC SPValidateTable
AS
SELECT COUNT(*) AS DuplicateNames
FROM (
SELECT COUNT(*) AS Duplicates
FROM dbo.MyTable
GROUP BY Name
HAVING COUNT(*) > 1) AS Dups
RETURN
GO
Alternatively, you can selectively check only those names (or dataset
DataRows) that were changed:
CREATE PROC SPValidateName
@.Name varchar(100)
AS
SELECT COUNT(*) AS DuplicateNames
FROM (
SELECT COUNT(*) AS Duplicates
FROM dbo.MyTable
WHERE Name = @.Name
GROUP BY Name
HAVING COUNT(*) > 1) AS Dups
RETURN
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Tris" <Tris@.discussions.microsoft.com> wrote in message
news:06E8D3D0-C416-46E4-9369-21405864428C@.microsoft.com...
>
> "Dan Guzman" wrote:
>
> The unique restrictions are enforced in the dataset, but i use a tower of
> babel switch,
> X = 'X'
> Y = 'Y'
> Save
> X = 'Q'
> Y = 'X'
> X = 'Y'
> Save
> The constraints on the dataset are never breached.
> I can't delete the rows, it would cause far to many headaches. And
> dissabling the restrictions would be a pain.
> However, i suppose i could create a validate stored procedure...
> What do you think of this:
> SPValidateTable?()
> #TempTable
> (
> VarChar Name,
> Int Count
> )
> TempTable = SELECT DISTINCT Name, COUNT(Name) FROM Table
> SELECT Count(Name) FROM #TempTable WHERE (Count > 1)
> Return
>
> If the result != 0
> Throw exception.
> It's a hack i know... but it could work.
> How could this be implemented before / After commiting a transaction, so
> that it could fail a transaction if the method failed? And how can it's
> scope
> be handled in relation to the transaction?
> Tris|||Tracy McKibben wrote:
> Tris wrote:
> Assuming you know the two (or more) ID's that you want to swap:
> UPDATE tbl
> SET Name = CASE WHEN ID = 1 THEN 'BBB' WHEN ID = 2 THEN 'AAA' END
>
I forgot the WHERE clause, although on a small table, it should run fine
without one.
UPDATE tbl
SET Name = CASE WHEN ID = 1 THEN 'BBB' WHEN ID = 2 THEN 'AAA' END
WHERE ID IN (1, 2)
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||> Assuming you know the two (or more) ID's that you want to swap:
> UPDATE tbl
> SET Name = CASE WHEN ID = 1 THEN 'BBB' WHEN ID = 2 THEN 'AAA' END
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
Hi Tracy,
Thanks, that's interesting, but unfortunately, it's done using the MS
Dataset update mechanism, so if two elements are effectively swapped in code
,
then they are not aware of each other.
Tris|||Hi Dan,
Thanks, yes that makes sense.
Can this be run inside a transaction to check before i call the Commit?
I'm not sure how the scope will affect things, i mean, a transaction can't
see what other transactions are doing, so if i ran two concurrent
transactions, and ran the test first, it passed and then i commitied, the
second one may fail when the test runs?
But as far as i can see it seems viable.
Tris