Re: Best way to override AllowNull in a schema?

Looking it over, I think I've figured out what's happening. In Oracle,
the autonumber sequence is external to the column - it's just a
sequencer object. In Sql Server, the sequence is tied to the column.
Thus, when ADO.Net fetches the schema for the dataset, it can
automatically fetch the maximum value of the autonumber and properly
configure the AutoIncrementSeed, AutoIncrementStep values based on the
schema.

In Oracle, it can't get that information automatically. I would have
to call a second query to find out what the current value of my
sequencer is to get the current AutoIncrementSeed. I could do a decent
faking of it with something like Max(myAutonumberColumnValue) + 1 as
the seed and set that as the AutoIncrementSeed.... either way though,
I'm creating values that will be thrown away when the data enters the
table, so I'm just using negatives. It works well.

Thanks for all your help. Personally, I'm thinking that the idea of
applying constraints to values that will never actually enter the
database is silly - but you've been extremely helpful at understanding
how things work within the dataset (and it's relation to the data
adaptors) - and found a workable solution to my problem.

Martin Z wrote:
Ah, I misunderstood. I imagined the behaviour for an autoincrement to
be "must be null for new rows, and apply primary key constraint for
existing ones" or something. Either way, I can't post the code because
I'm a new developer working on a large old company app (ported forward
from VB), trying to reapply their old crudscreen tool to a new table
I've added. So, my tweaks to the tool are here-there-and-everywhere,
unfortunately.

The crux of the matter is that the autoincrement system creates a
number that violates the uniqueness constraint of the table. I have a
table with 3 rows, primary key values 1, 2 and 3. When I create a new
row and attempt to add it to my dataset, it has a value of 2 that the
autoincrement column has autogenerated - which, logically, the system
rejects.

The idea of having to generate a
"unique-within-my-dataset-but-not-necessarily-within-the-actual-table"
value that will be replaced anyways seems very hackish, but I suppose I
have to satisfy the constraint on the dataset.

I'll try the "start at -1 and decrement" approach.

Dave Sexton wrote:
Hi Martin,

Oop, I missed the tail end of your message. Yes, the value should
always be null until the commit, when it should be populated.... but
this behaviour is not happening.

No, it shouldn't be null. If the column is Unique, then DBNull for every new
value wouldn't satisfy that constraint. AutoIncrement creates a new number
that is temporary, and preserves the uniqueness of newly added rows until you
can perform the update and retrieve the actual value. The behavior you have
described is exactly what I would expect from an AutoIncrement column.

If I create an empty datarow and set
the other (non-autoincrementing-key-fields) it attempts to put a value
in the key field too - either way it violates constraints. I can't
even get to update, because it complains the moment I add the row to
the table.

The values chosen by the DataTable for newly added rows should always be
unique (I've never had a problem), so I'm curious to know what error you're
getting when adding a new row. Care to post the code and the exception?

The behaviour I want is obvious - the autoincrement field
should be stuck at DBNull until update, at which time the field should
be fixed at the value that Oracle provided

That would violate the Unique constraint, so it's impossible. If you want to
relax the constraints then you don't need the AutoIncrement column either, but
I wouldn't recommend that approach.

Unfortunately, for some
reasons datasets don't provide that mechanism for me - maybe it only
works like that when using SqlServer - I don't know well enough how
DataSets work under the hood.

This particular issue you're having has nothing to do with the RDBMS. The
DataSet and the database are distinct entities. AutoIncrement and its
behavior belongs solely to the DataSet, which has no relationship to the
database or the data provider being used.

--
Dave Sexton

.

Relevant Pages

  • Re: Please help with no-gap autoincrement field
    ... My boss wants to have an autoincrement index with no gaps, ... I am new to ORACLE and based on my limited knowledge, ... autoincrement field with no gaps? ... as the source of the key rather than a sequence. ...
    (comp.databases.oracle.misc)
  • Re: Best way to override AllowNull in a schema?
    ... properties - it doesn't seem to allow the constraint to be violated, ... imperfect mapping of ADO.Net to Oracle. ... Commonly, one would just set AutoIncrement, ReadOnly and Unique all to true on ... the sequenced column. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Best way to override AllowNull in a schema?
    ... I imagined the behaviour for an autoincrement to ... number that violates the uniqueness constraint of the table. ... described is exactly what I would expect from an AutoIncrement column. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Best way to override AllowNull in a schema?
    ... which I assume it pulls from schema in SQL ... number that violates the uniqueness constraint of the table. ... autoincrement column has autogenerated - which, logically, the system ... described is exactly what I would expect from an AutoIncrement column. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Propping up the theory of Evolution
    ... Meaning that us diploids have twice that number. ... sequence constraint because it does not code for proteins (or some ... Since this is the part under some sequence constraint ... conservative nature of selection. ...
    (talk.origins)
Loading