[Mono-dev] [ADO.NET] AutoIncrement fields, unique constraints and updates

[Marek Habersack]

On Sun, Mar 05, 2006 at 03:07:02AM -0500, T Senganal scribbled:
> Hi 
> 
> >>Everything works fine until I try to update a row
> >>and store the modified dataset back in the database. At this point,
> >>UniqueConstraint throws an exception for the row being updated,
> claiming
> >>that its auto-increment field's value is taken (which is,
> technically, true
> >>as this field is the one that contains the "non-unique value").
> 
> The UpdateCommand generated by CommandBuilder shouldn't try to modify
> an auto-increment field on the database.. 
> This is definitely a bug ..  please file a bug report.. 
That's one thing, but I'm thinking that it doesn't really matter in this
case. It's OK to set the auto-increment column to a special value (usually a
0) to have the DBMS increment it according to the sequence state, but that
won't work for us since the UniqueConstraint (in case the auto-incremented
column is a primary key or unique) will throw an exception (unjustified in
this case).

The way UniqueConstraint enforces the constraint currenty causes it to throw
an exception for any auto-increment column in a row of any state. For
instance, if you have a row with an auto-increment column with value 1 and
you update the row's data (with the exception of the auto-increment column's
value) then a version of the row with value 1 for the auto-increment is
created and that triggers UniqueConstraint exception throw.

Looking through the DataRow, UniqueConstraint, DataTable and Index code, it
seems that the best place to check for auto-increment columns would be the
Index class (System.Data.Common/Index.cs) since its HasDuplicates property
is used in the UniqueConstraint AssertConstraint method. It should probably
compare the auto-increment fields only between the rows in the same state
and, perhaps, ignore duplicates in such columns when they contain a special
value.

I'm not sure how MS.Net works in this regard as I haven't tested it (don't
have Windows handy atm) and I don't know whether all the DBMS-es out there
use 0 as the special value of the auto-increment column. Perhaps the
dupe-check code should completely ignore the values of auto-increment
columns and the specific DBMS command builders should ignore such columns
alltogether.

As my knowledge of the Mono internals is very small, I'm in no position to
suggest any solutions, so if any of the above suggestions seem dumb, just
ignore them. But definitely there is a problem in the way this situation is
handled right now.

> Not sure, if this  solves the problem .. Pls post some sample code..
Not really much code is necessary. Generate a typed dataset from the
attached XSD, create a corresponding table in your database, put 2-3 records
inside, load them all up into a dataset, modify the quantity field and try
to commit the change to the database. Or try to delete a row. Both should
throw the UniqueConstraint exception.

best regards,

marek
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 191 bytes
Desc: Digital signature
Url : http://lists.ximian.com/pipermail/mono-devel-list/attachments/20060305/367b9e33/attachment.bin